SQL Server 2008 - script data using Scripter.EnumScript fails with "Login failed for user" error

Question

I am trying to script data of a table.

If I use windows authentication (Integrated Security=true), code works.

If I switch to sql authentication with existing user and password, it fails trying to access returned scripts enumerable. The user is an SQL login and admin.

Simplified version of code is here:

    //string connectionString = "Data Source=myserver; Initial Catalog=mydb; Integrated Security=True";            
    string connectionString = "Data Source=myserver; Initial Catalog=mydb; Integrated Security=False; User ID=user1; Password=1234;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        ServerConnection serverConnection = new ServerConnection(connection);
        Server server = new Server(serverConnection);

        Database database = server.Databases["mydb"];
        Collection<SqlSmoObject> tables = new Collection<SqlSmoObject>();
        tables.Add(database.Tables["mytable"]);

        Scripter scripter = new Scripter(server);
        scripter.Options.ScriptData = true;
        scripter.Options.ScriptSchema = false;
        var scripts = scripter.EnumScript(tables.ToArray());

        Collection<string> coll = new Collection<string>();
        foreach (var item in scripts) // error is here
        {
            coll.Add(item);
        }
    }

Sometimes it fails with this exception:

  System.Data.SqlClient.SqlException occurred
  Message=Login failed for user 'user1'.
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=14
  LineNumber=65536
  Number=18456
  Procedure=""
  Server=myserver
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
  InnerException:

And less often - with this:

System.Data.SqlClient.SqlException occurred
  Message=A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=20
  LineNumber=0
  Number=233
  Server=myserver
  State=0
  StackTrace:
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  InnerException: 

Any idea, why that happens?

Answer 1

Looks like a Scripter bug.

Scripter probably closes existing connection and reopens another, using ConnectionString property of the original connection. Of course, if security info is not persisted, that connection string does not have password information.

So the workaround is to create a connection with security info persisted:

string connectionString = "Data Source=myserver; Initial Catalog=mydb; Integrated Security=False; User ID=user1; Password=1234; Persist Security Info=True;";
// etc.