April 13, 2010 at 8:25 am
Situation:
ServerName1 - SQL 2005 default instance
ServerName2\sql1 - SQL 2005 named instance
Logged in as Windows account with SysAdmin rights
Created link from ServerName1 to ServerName2\sql1 using the following commands:
EXEC sp_addlinkedserver @server='APPNAME',@srvproduct='',@provstr='ServerName2\sql1',@catalog='Database_A',@provider='SQLNCLI'
EXEC sp_addlinkedsrvlogin @rmtsrvname='APPNAME',@useself='false',@locallogin=NULL,@rmtuser='App_Data_A',@rmtpassword='ADApassword'
Link created successfully
Problem:
Right-click on link to test connection as user that created link (WindowsID & SysAdmin) and the test succeeds.
Connect to ServerName1 as SQL Login 'APP_USER1'
Right-click on link to test connection as user 'APP_USER1' and test fails with following error:
"Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server Error: 7416)"
If I create the link and set the variable @server='ServerName2\sql1' (Actual name of target server) then the test connection using 'APP_USER1' works perfectly.
I can create links to SQL 2000 or Oracle servers and use any name I want to for @server and everything works fine. Only links from SQL 2005 to SQL 2005 seem to have this problem for me.
Mapping individual logins does not solve this problem.
Can't seem to find anything on the Internet that addresses this particular issue. Everything I can find assumes I want to use the actual name of the target server.
April 13, 2010 at 8:41 am
My sincere advice to you is abandon linked server. It poses a security threat and it's only use is to circumvent doing it the right way by doing it the easy way. Either use SSIS, mirroring, replication, C#, or even openrowset with passthrough security if you must.
April 13, 2010 at 8:56 am
Toby White (4/13/2010)
My sincere advice to you is abandon linked server. It poses a security threat and it's only use is to circumvent doing it the right way by doing it the easy way. Either use SSIS, mirroring, replication, C#, or even openrowset with passthrough security if you must.
Thanks, but not an option for political reasons I have no control over.
April 13, 2010 at 9:20 am
Why can't you at least use pass through security via Kerberos instead of supplying username and password in cleartext. This way you wouldn't have to create a separate linked server for each application that must connect to the other server just to mimic the connection details appropriately.
April 13, 2010 at 11:16 am
Through experimentation I now have a workaround.
Create an alias on ServerName1 that equals "DesiredLinkName" with correct IP Address and port.
Create a standard SQL Server link and specify the alias as the link name.
Still don't know why it won't work intuitively, but this solves my problem.
April 13, 2010 at 11:42 am
Toby White (4/13/2010)
My sincere advice to you is abandon linked server. It poses a security threat and it's only use is to circumvent doing it the right way by doing it the easy way. Either use SSIS, mirroring, replication, C#, or even openrowset with passthrough security if you must.
Why do you think that Linked Servers are any less secure than Replication or OpenRowset?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 13, 2010 at 12:31 pm
Why do you think that Linked Servers are any less secure than Replication or OpenRowset?
Replication is more secure because it is managed, automated, and you can use an active directory account so you don't have to send user/password in cleartext. The security of Openrowset is subject to the details of the connection string and whether an AD account (trusted). opening the "Distributed Adhoc Queries" is necessary, but a relatively small concession. Conceivably you could run sp_configure to modify the value before and after all procedures that use it. Admittedly you could do the same thing with linked servers calls - check for existence and create/drop in procedures that use it.
However, in practice linked servers tend to stay enabled and available for general usage. Using passthrough/trusted security it's not as bad. Using SQL accounts is bad, but then again that isn't inherent in linked servers. There is only a small difference between openrowset and linked servers with slightly better security going to openrowset because it is generally more controlled in practice.
One other related problem with linked servers, in practice, is that they tend to get overused by users. Performance is almost never good with queries that join tables across servers. If the optimizer hauls the entire table across it might be so big that it has to be stored on disk and then reread via hash join.
Do I think there is ever a time to use linked servers - yes, it's just that it should be the last option if there are problems with all the other options. The factors I would generally consider are:
Performance
Latency requirements
Security
I usually find that either the databases should be on the same server, or that one of the other options is better.
April 13, 2010 at 1:17 pm
Toby White (4/13/2010)
Why do you think that Linked Servers are any less secure than Replication or OpenRowset?
Replication is more secure because it is managed, automated, and you can use an active directory account so you don't have to send user/password in cleartext...
Linked Servers certainly have as much security management as replication, and I don't think that automation really has anything to do with the security aspect. As for the AD accounts, you are right, the two-hop rule will prevent the use of Windows Trusted authentication for client-connected sessions. However, 1) this does not affect automated, background sessions (like SQL Agent), but more importantly, 2) if you really care about security you should be using Kerberos instead of Windows authentication anyway. Linked server definitions can use Kerberos as its trusted logon mechanism and Kerberos is far more secure than Windows authentication could ever hope to be.
The security of Openrowset is subject to the details of the connection string and whether an AD account (trusted). opening the "Distributed Adhoc Queries" is necessary, but a relatively small concession. Conceivably you could run sp_configure to modify the value before and after all procedures that use it. Admittedly you could do the same thing with linked servers calls - check for existence and create/drop in procedures that use it.
Yep, but Linked Server can use AD accounts in all of the same cases that Openrowset can (ie., when not violating the two-hop rule, again, Kerberos fixes this and is more secure to boot). In fact, AFAIK, Openrowset is just an ad-hoc, unmanaged version of Linked Servers. And because it's unmanaged, it's actually less secure, not more secure. With linked servers you at least have some control over who can use what Server and what remote accounts they must use.
Granted, that control may be far less than we'd like, but I really don't see anything available inside of SQL Server that offers a better security solution, certainly not Openrowset which does the same thing as a Linked Server definition but with less control.
However, in practice linked servers tend to stay enabled and available for general usage. Using passthrough/trusted security it's not as bad. Using SQL accounts is bad, but then again that isn't inherent in linked servers. There is only a small difference between openrowset and linked servers with slightly better security going to openrowset because it is generally more controlled in practice.
I would say that it's exactly the other way around. Again, AFAIK, there's nothing that can be done with Openrowset that cannot be done as securely or (usually) more securely with Linked Servers.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 14, 2010 at 5:32 am
Interesting discussion concerning the ins and outs of security of Linked Server vs Other Approaches, but I'm glad I didn't wait for somebody to help me with a solution to my problem. :Whistling:
April 14, 2010 at 7:52 am
HeH - well the important thing is that you have a solution. I am glad you found it and it did seem a little wrong to be debating over something that was only loosely related to your issue on your thread.:hehe:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply