July 11, 2013 at 10:26 am
I am a contractor DBA, and we have about 60 SQL servers of all versions (I mean, all, include 7) and flavors (desktop, express, std, ent...) I am trying to connect to all of them, either via scripts or linked servers for the purposes of collecting data on them. Some of these servers have no other accounts than 'builtin\administrator' and sa, but I can connect to them either via SQLCMD or SSMS (or Enterprise Manager) either from my desk (I have SSMS 2012 installed) or on the servers desktop, since I am a domain admin. So, there are no servers that I don't have some access to.
But, in trying to create a linked server to a handful of these, I haven't been able to find any combo of provider, provider string, driver, etc, that will allow me to create a linked server. The SQL server I am trying to link from is 2012 Std.
I know anyone will need more info to help me with this, so this is just a start.
Thanks,
David
July 12, 2013 at 2:57 am
You will get plenty of errors connecting to linked servers if security is not setup correctly. If you are trying to use impersonation you need to ensure SPNs are registered correctly. If you are trying to use stored credentials you need to ensure those credentials are present on the linked instance. If you are trying to use SQL login credentials you need to ensure that the login exists on the linked server and that the server is in mixed mode authentication.
What errors are you getting?
Joie Andrew
"Since 1982"
July 14, 2013 at 3:28 pm
david.griswold (7/11/2013)
But, in trying to create a linked server to a handful of these, I haven't been able to find any combo of provider, provider string, driver, etc, that will allow me to create a linked server. The SQL server I am trying to link from is 2012 Std.
Yes, setting up a linked server from SQL 2012 to SQL 2000 or SQL 7 is difficult. Not to say impossible. Microsoft only supports two versions back, so the SQLNCLI11 provider does not support connections to SQL 2000. I've tried forcing use of the SQLNCLI10 provider, but the error message I got was funny.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply