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.

Thanks for the edits guys. I will remember to put the code into blocks the next time. — davehay, Nov 24 '16 at 08:36

score 8 · Answer 1 · edited May 26 '20 at 21:12

8

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

edited May 26 '20 at 21:12

d219

2,707
5
31
36

answered Nov 02 '18 at 10:24

Nullkiller

91
1
4

Thanks. I have spent 2 days working out why the outer application could authenticate but the SQLBulkCopy couldn't. Persist Security Info=True solved it. – William Smith Sep 12 '22 at 10:08

score 3 · Accepted Answer · answered Nov 24 '16 at 07:26

3

The solution is quite straightforward but I am still interested to know why SQL server authentication should be different from Windows authentication.

   using (SqlTransaction transaction =
                sqConn.BeginTransaction())
            {

                SqlBulkCopy sbc = new SqlBulkCopy(sqConn,SqlBulkCopyOptions.KeepIdentity,transaction);
                sbc.DestinationTableName = file;
                sbc.BatchSize = 1000;
                sbc.NotifyAfter = 1000;
                sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                sbc.WriteToServer(SourceTable);
                transaction.Commit();
            }

answered Nov 24 '16 at 07:26

davehay

195
1
9

What is the solution then? AFAICT you didn't change the authentication. – martennis Jul 14 '17 at 15:00
1

The solution is to use a transaction then it works with SQL Server authentication. I needed this to bulk copy to Azure SQL tables where Azure AD authentication was not a possibility. – davehay Aug 08 '17 at 08:19
`new SqlBulkCopy()` has 4 overloads: `SqlConnection` only, `SqlConnection/SqlBulkCopyOptions/SqlTransaction`, `string` only, and `string/SqlBulkCopyOptions`. If you want to supply the copy option KeepIdentity, then you will have to supply something for the SqlTransaction, otherwise the compiler will try to match on the string/SqlBulkCopyOptions overload. As far as I know, there's nothing stopping you from just passing a null object for the SqlTransaction: `new SqlBulkCopy(sqConn, SqlBulkCopyOptions.KeepIdentity, null)` – Thorin Jan 09 '18 at 00:35
I should also say, I don't know why but the SqlBulkCopy class just doesn't work with a connection string using sql server authentication (username and password in the connection string), so when your connection string contains sql server authentication you have to pass an existing SqlConnection object to the SqlBulkCopy object. It's weird, but that's just how it is. – Thorin Jan 09 '18 at 01:12
For info the reason it works with Windows authentication is there is no password contained in the connection string, when a connection has been used once then the password is stripped (so in SQL Server authentication you no longer have a valid connection string in the connection string of the connection). I would assume the OP was making another call with 'sqlConn' before doing the bulk copy and this will have stripped the password by default - see https://stackoverflow.com/questions/12467335/connectionstring-loses-password-after-connection-open – d219 May 26 '20 at 21:11

score 0 · Answer 3 · edited Sep 13 '19 at 13:21

Try this it worked for me

private static void BulkInsert(DataTable dtExcel, SqlConnection con)
        {
            try
            {
                {
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    var sqlTransactionScope = con.BeginTransaction();

                    //Open bulkcopy connection.
                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransactionScope))
                    {
                        //Set destination table name
                        bulkcopy.BulkCopyTimeout = 0;
                        bulkcopy.BatchSize = 1000;
                        bulkcopy.DestinationTableName = "[dbo].[cc_alertowner]";

                        try
                        {

                            foreach (DataColumn col in dtExcel.Columns)
                            {
                                bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
                            }
                            // bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("", ""));
                            // bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("DateCreated", "DateCreated"));

                            if (con.State == ConnectionState.Closed)
                                con.Open();

                            bulkcopy.WriteToServer(dtExcel);
                            sqlTransactionScope.Commit();
                        }
                        catch (Exception ex)
                        {
                            sqlTransactionScope.Rollback();
                            throw;
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

It looks like an implementation of the solution I described in the original question which is to wrap the copy within a transaction. As has been explained in the original answer it is to do with passing the password as part of the connection string. After the first use of the connection string, the password is stripped from the string and causes the failure. There are no passwords involved with windows authentication. An alternative solution was to use persist security info = true in the connection string which has the same effect. — davehay, May 28 '20 at 05:03
The interesting bit was it only happened where identity was being preserved. — davehay, May 28 '20 at 05:09

score -1 · Answer 4 · answered Nov 24 '16 at 07:10

-1

Looks Like your SQL Server Mixed Mode authentication is turned off.

Right Click your DB instance and select Properties. Click on Security and in Server Authentication select second radio button SQL Server and Windows Authentication Mode.

After this Please restart SQL service from services.msc

answered Nov 24 '16 at 07:10

Programming Geek

244
5
15

If that was the case the second format of the SQLBulkCopy would not work either. – davehay Nov 24 '16 at 07:15
@davehay : Can you please show connection string you are using here in Config file – Programming Geek Nov 24 '16 at 07:22
check if you have "integrated security" part in your connectionstring. if so, try remove the "integrated security" part from your db connectionstring and try again. This is setting your connection to use the windows authentication instead of the sql authentication. – Programming Geek Nov 24 '16 at 07:29
1

I down voted you reluctantly as you have not paid attention to the question or my comments. – davehay Nov 24 '16 at 08:35

SqlBulkCopy Login Failed

4 Answers4