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

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

i trying script info of table.

if utilize windows authentication (integrated security=true), code works.

if switch sql authentication existing user , password, fails trying access returned scripts enumerable. user sql login , admin.

simplified version of code 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 here { coll.add(item); } }

sometimes fails exception:

system.data.sqlclient.sqlexception occurred message=login failed user 'user1'. source=.net sqlclient info provider errorcode=-2146232060 class=14 linenumber=65536 number=18456 procedure="" server=myserver state=1 stacktrace: @ system.data.sqlclient.sqlinternalconnection.onerror(sqlexception exception, boolean breakconnection) @ system.data.sqlclient.tdsparser.throwexceptionandwarning(tdsparserstateobject stateobj) @ system.data.sqlclient.tdsparser.run(runbehavior runbehavior, sqlcommand cmdhandler, sqldatareader datastream, bulkcopysimpleresultset bulkcopyhandler, tdsparserstateobject stateobj) @ system.data.sqlclient.sqlinternalconnectiontds.completelogin(boolean enlistok) @ system.data.sqlclient.sqlinternalconnectiontds.attemptonelogin(serverinfo serverinfo, string newpassword, boolean ignoresniopentimeout, int64 timerexpire, sqlconnection owningobject) @ system.data.sqlclient.sqlinternalconnectiontds.loginnofailover(string host, string newpassword, boolean redirecteduserinstance, sqlconnection owningobject, sqlconnectionstring connectionoptions, int64 timerstart) @ system.data.sqlclient.sqlinternalconnectiontds.openloginenlist(sqlconnection owningobject, sqlconnectionstring connectionoptions, string newpassword, boolean redirecteduserinstance) @ system.data.sqlclient.sqlinternalconnectiontds..ctor(dbconnectionpoolidentity identity, sqlconnectionstring connectionoptions, object providerinfo, string newpassword, sqlconnection owningobject, boolean redirecteduserinstance) @ system.data.sqlclient.sqlconnectionfactory.createconnection(dbconnectionoptions options, object poolgroupproviderinfo, dbconnectionpool pool, dbconnection owningconnection) @ system.data.providerbase.dbconnectionfactory.createpooledconnection(dbconnection owningconnection, dbconnectionpool pool, dbconnectionoptions options) @ system.data.providerbase.dbconnectionpool.createobject(dbconnection owningobject) innerexception:

and less - this:

system.data.sqlclient.sqlexception occurred message=a connection established server, error occurred during login process. (provider: shared memory provider, error: 0 - no process on other end of pipe.) source=.net sqlclient info provider errorcode=-2146232060 class=20 linenumber=0 number=233 server=myserver state=0 stacktrace: @ system.data.sqlclient.sqlinternalconnection.onerror(sqlexception exception, boolean breakconnection) innerexception:

any idea, why happens?

looks scripter bug.

scripter closes existing connection , reopens another, using connectionstring property of original connection. of course, if security info not persisted, connection string not have password information.

so workaround create connection security info persisted:

string connectionstring = "data source=myserver; initial catalog=mydb; integrated security=false; user id=user1; password=1234; persist security info=true;"; // etc.

sql-server-2008 smo