Property LoginSecure cannot be changed or read after a connection string has been set.

Question

hi,
      i am trying to copy a table from one database to another using SMO's Transfer. I am getting the following error when i execute the Transfer.TransferData()

"Property LoginSecure cannot be changed or read after a connection string has been set."
i am using .net 2.0(x64),sql 2005 std edition

please help....................

Answer 1

Hello,

Please, could you post your code ?
Also, could you tell us the last Service Pack you applied to your Sql Server ? ( you may have problems with 2005 SP1 )

Could you tell us whether your 2 databases are belonging to the same instance ?

We are waiting for your feedback to try to help you more efficiently

Have a nice day

PS : I suppose that you are using VS to write your code.Could you tell us which are the edition,version and SP of your VS.
I have noticed that yoy are using .Net 2.0 (X64). Are all your softwares X64 ( Sql Server,VS...) ?

Please, could you have a look on this link ?
http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/d670f19e-bdf2-4420-99e0-df7958c81422/

There is an example of the use of TranferData

I have already seen your problem on this forum ( at least a year ago ).I will try to retrieve the related thread.

Answer 2

Hello,

Please,could you have a look on this thread ?
http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/a4eb9857-c112-43fe-8fc1-149b25c12113

the thread is beginning with another problem but it is related to your problem ( with LoginSecure ).
The solution seems to be in the 23th post ( around )

Don't hesitate to post again for more help or explanations

Have a nice day

Answer 3

hey ,

         thanks a lot for replying
The 2 databases belong to the same instance.

here are the details of sql server i am using

vs2008 is x64 and sql 2005 is a 32-bit 


Microsoft SQL Server Management Studio      9.00.1399.00
Microsoft Analysis Services Client Tools      2005.090.1399.00
Microsoft Data Access Components (MDAC)      6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML      3.0 5.0 6.0
Microsoft Internet Explorer      7.0.6001.18000
Microsoft .NET Framework      2.0.50727.3074
Operating System      6.0.6001

i am using VS2008 with sp1 installed

VS2008 details below

Microsoft Visual Studio 2008
Version 9.0.30729.1 SP
Microsoft .NET Framework
Version 3.5 SP1
Installed Edition: Professional

i am  posting the code below


 

Dim dcstring As String = "Data Source=" + dsource + ";User id=" + Me.Getname + ";Password=" + Me.Getpass + ";Integrated Security=false"
Dim dcon As New SqlConnection(dcstring)
Dim srv As New Server(New ServerConnection(dcon))
Dim srcdb As Database
srcdb = srv.Databases(Me.SourceDbName)
Dim xfr As Transfer
xfr = New Transfer(srcdb)
xfr.DropDestinationObjectsFirst = True
xfr.CopyAllObjects = False
xfr.CopyAllTables = False
xfr.Options.ContinueScriptingOnError = True
xfr.ObjectList.Add(srcdb.Tables(Me.Getsourcetable.TableName, Me.SourceSchemaName))
xfr.CopySchema = True
xfr.Database = srcdb
xfr.DropDestinationObjectsFirst = True
xfr.DestinationDatabase = dbname
'xfr.DestinationLoginSecure = False
'i have tried enabling and disabling LoginSecure
xfr.DestinationServer = srv.Name
'i tried giving the server name manually also
xfr.DestinationLogin = Me.Getname
xfr.DestinationPassword = Me.Getpass
xfr.TransferData()



i also tried using xfr.scriptTransfer() and successfully generated the script but was not able to execute it either in sql2005 or using ExecuteNonQuery()
it was generating some syntax error.......


once again thankyou for replying ...
i am new to SMO and almost gave up on it....

Answer 4

Hi

The DestinationLoginSecure property need to be set to true then the destination server uses the Windows Authentication. In case if it set to false, it is using the SQL Server Authentication.

Refer http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.destinationloginsecure.aspx

Seems like the destination server is same as the source server. Hence I feel you can use the same authentication which is used for the source database as well.

 

Thanks

Sreekar

Answer 5

hai there

                 Its not the problem with destinationLoginSecure...thats for sure
even if i enable the sql server authentication ..i still get the same error
source db is connecting fine...but i dont know what is happening with the destination db....

Answer 6

Hi

Can you please post me the error you are getting after removing the LoginSecure property from the code?

Thanks
Sreekar

Answer 7

hi,
        same error i am getting....
"Property LoginSecure cannot be changed or read after a connection string has been set."

Source :"Microsoft.SqlServer.ConnectionInfo"

Inner Exception :Nothing

Answer 8

Check wheather SQL Server agent is started or not.. I also had the similar issue and i figured it out that this was an issue

Answer 9

I had the same problem and it took me a while to get to this workaround:

        Dim csb As New SqlConnectionStringBuilder(sql_con.ConnectionString)
        If csb.IntegratedSecurity Then
            server_con = New ServerConnection(sql_con.DataSource)
        Else
            server_con = New ServerConnection(sql_con.DataSource, csb.UserID, csb.Password)
        End If
        server_con.Connect()
        server = New Server(server_con)

Maybe there is a bug so that if you use the constructor with the SqlConnection (new server_con(sql_con)) later some code wants set server_con.LoginSecure. And that throws an exception because the connection is already opened. You get a similar error if you try to change server_con.LoginSecure after you opened the connection.