I create my connection string like this:
var connectionString = "Data Source=MyPC\\SQLExpress;Initial Catalog=TestDb;Integrated Security=false;User Id=TestUser;password=TestPwd"
I create a SQL connection like this:
var sqlConnection = new SqlConnection(connectionString);
I then create my SqlBulkCopy object like this:
var bulkData = new SqlBulkCopy(sqlConnection.ConnectionString, SqlBulkCopyOptions.KeepIdentity);
The problem is that I'm getting error 18456, Status 8 error message when the bulk copy tries to execute. This error indicates a password mismatch. Yet, I can log into the database with the exact credentials presented above.
So, I stepped through my program and noticed this: The connection string, in debug, looks as it should, with the user ID and password. However, when I hover my cursor over the bulk copy's sqlConnection.ConnectionString, it shows the entire connection string WITHOUT the password. It only has the user! For some reason, it appears that my password is not being used when I create the SQL Connection using the connection string that has the password in it. So, on a hunch, I changed the code to look like this:
var bulkData = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity);
And it worked fine! So, my question is, why is it that when I create the SQL Connection with a connection string that has a password in it, the password isn't included in the resulting SQL Connection's ConnectionString property?
Unless you include Persist Security Info = True in the connection string, SqlConnection will strip the password for security reasons.