Wednesday, March 03, 2010 10:40 AMI am trying to add Linked server(sql server) to the Sql server by using SMO. I am successful to add Linked server but I am unable to execute the Sql queries because of authentication/authorization exception. I went to Sql server by using Sql Server Management Studio (SSMS) and can see the created linked server exists.
When I open the properties of linked server, inside the Security page (top left pane), there is the list of Login mappings (which can be created by LinkedServerLogin class). Under the list of logins, there are options as following:
For a login not defined in the list above, connection will:
- Not be made
- Be made without using a security context
- Be made using the login's surrent security context
- Be made using the security context:
With password: Text box
When I set through SSMS the Remote login and password, I can run queries in SSMS.
My question is, How to set these options through SMO programmatically ?
Any help will be highly appreciated. Thanks in advance
Friday, March 05, 2010 6:56 PMAnswererHi,
For logins not defined in lthe mapping as you can see the statement generated is
master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'linked_server_name ', @locallogin = NULL , .. other options like impersonation and passwd etc.
So this can be generated through SMO by creating a LinkedServerLogin whose name property is string.empty.
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
Saturday, March 06, 2010 4:26 AMDear Alok Parmesh,
Thank you for your reply. Actually I already found this way to set the remote login for Linked server, but I was looking, if there is way to set these options through SMO, not through sql statement. This way is same as we get TABLES information through sql statement rather than we use TABLES property of database.
Anyway, thank you very much for your reply.
Aftab Ahmed Kalhoro
Saturday, January 07, 2012 12:02 AMYou said you found an answer, can you post it?
Tuesday, January 10, 2012 6:30 PMIn Microsoft.SqlServer.Management.Smo.LinkedServerLogin you can set the RemoteUser property and the SetRemotePassword method.