SqlBulkCopy Login Failed

Question

I have an issue with SqlBulkCopy command when using SQL Server authentication. The issue does not arise with Windows authentication.

SqlBulkCopy sbc = new SqlBulkCopy(sqConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity);

this throws an error:

Login failed for user 'xx'

Code:

SqlBulkCopy sbc = new SqlBulkCopy(sqConn);

This works fine but does not preserve identity column original values.

Answer 1

"persist security info=true" is required in the connection string. Otherwise password is stripped from sqlConn.ConnectionString if the connection is already open.