Linked server - from 2008 to a 2005 box.
in [Server Configuration]
|
From: Steen Schlüter Persson on 10 Jan 2010 11:37 From what FSB have said earlier, he is using the "Be made using this security context" in the wizard. This requires a SQL login and password to be entered. When this authentication method is being used, it doesn't matter how you connect to the "source" server. Every connection from here to the remote server is being done by logging on as the SQL account that has been specified (in this case "sa"). I have never experienced that this should work differently depending in how you log on to the source server. As mentioned earlier, I don't know if something has changed in this area in SQL2008 though since I haven't had the change to test this. I'd still like to know though if a connection can be made to the "target" server using the sa account and the password specified in the Linked Server( from e.g. SSMS)? Next step could also be to create the linked server by using the sp_addlinkedserver. If it's between two SQL servers, the syntax is quite simple. The below two lines will create a linked server and add "sa" as SQL login for the linked server EXEC sp_addlinkedserver 'TargetServerName', N'SQL Server' EXEC sp_addlinkedsrvlogin 'TargetServerName', 'FALSE', NULL, 'sa', 'YourSApassword' You can also look up the syntax in BOL to see the other options. The above should work no matter how you connect to the source server - at least it has done for me in many years..:-). -- Regards Steen Schlüter Persson (DK) "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CFC7C376C71BYazorman(a)127.0.0.1... > Fsb (Fountainhead7(a)hotmail.com) writes: >> The issue im finding is that when I connect to the management studio >> using a windows account and then setting up a linked server using the SA >> account it fails. >> >> But If i connect to the management studio using the SA account AND then >> try to setp the a linked server using the SA acoount it works! >> >> So for some reason HOW I connect to the Managment Studio is causing the >> issue... >> >> My widows account is is part of the sysadmin role....SO it should work.... > > Not necessarily. > > Setting up a linked servers includes two steps. First define the linked > server with sp_addlinkedserver, second set up login mapping with > sp_addlinkedsrvlogin. > > It is not clear from your what you have done, but it seems that you > have used the dialogs in SSMS to set up the linked server. I never use > those dialogs, so I can't what you should fill in. But you can use the > Script button to see what they generate. > > But default mapping is that if you are logged in on server A and run > a linked server against server B, server A will attempt to log in > server B with the same credentials. So if you are logged in as sa > on server A, and sa has the same password on server B, you are able to > connet. If the passwords are different, connection fails. > > If you are logged with a Windows login, then things gets trickier. > If server B and server A are in different domains, there is no trust > and things will not work out. But I believe that even if they are > in same domain, it may be difficult, as there are resitrictions with > double hops in Windows authentication. (Not only in SQL Server, but in > Windows in general.) > > The remedy is to setup a login mapping, so that your Windows login maps > to sa on server B. Just make sure that this mapping applies to your > login only, and not everyone on the server! > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Fsb on 14 Jan 2010 15:37 Thank you for understanding what I was saying :) Yes, a connection can be made to the "target" server using the sa account and the password specified in the Linked Server( from e.g. SSMS)? Only when I connect to SSMS using the SA account and then create the linked srever using the SA account for the target server. If I use my windows account (with sysadmin rights) to log into SSMS and try to create a liked server, again using the SA account for the traget server it fails! I can connect to any server and versions with no issues. the only difference with the target server is: 1. its on a SAN. 2. Its windows 2008 server OS. I will to try to create a linked server via SQL commands and let you know it it works. "Steen Schlüter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in message news:%23VMl8MhkKHA.6096(a)TK2MSFTNGP02.phx.gbl... From what FSB have said earlier, he is using the "Be made using this security context" in the wizard. This requires a SQL login and password to be entered. When this authentication method is being used, it doesn't matter how you connect to the "source" server. Every connection from here to the remote server is being done by logging on as the SQL account that has been specified (in this case "sa"). I have never experienced that this should work differently depending in how you log on to the source server. As mentioned earlier, I don't know if something has changed in this area in SQL2008 though since I haven't had the change to test this. I'd still like to know though if a connection can be made to the "target" server using the sa account and the password specified in the Linked Server( from e.g. SSMS)? Next step could also be to create the linked server by using the sp_addlinkedserver. If it's between two SQL servers, the syntax is quite simple. The below two lines will create a linked server and add "sa" as SQL login for the linked server EXEC sp_addlinkedserver 'TargetServerName', N'SQL Server' EXEC sp_addlinkedsrvlogin 'TargetServerName', 'FALSE', NULL, 'sa', 'YourSApassword' You can also look up the syntax in BOL to see the other options. The above should work no matter how you connect to the source server - at least it has done for me in many years..:-). -- Regards Steen Schlüter Persson (DK) "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CFC7C376C71BYazorman(a)127.0.0.1... > Fsb (Fountainhead7(a)hotmail.com) writes: >> The issue im finding is that when I connect to the management studio >> using a windows account and then setting up a linked server using the SA >> account it fails. >> >> But If i connect to the management studio using the SA account AND then >> try to setp the a linked server using the SA acoount it works! >> >> So for some reason HOW I connect to the Managment Studio is causing the >> issue... >> >> My widows account is is part of the sysadmin role....SO it should work.... > > Not necessarily. > > Setting up a linked servers includes two steps. First define the linked > server with sp_addlinkedserver, second set up login mapping with > sp_addlinkedsrvlogin. > > It is not clear from your what you have done, but it seems that you > have used the dialogs in SSMS to set up the linked server. I never use > those dialogs, so I can't what you should fill in. But you can use the > Script button to see what they generate. > > But default mapping is that if you are logged in on server A and run > a linked server against server B, server A will attempt to log in > server B with the same credentials. So if you are logged in as sa > on server A, and sa has the same password on server B, you are able to > connet. If the passwords are different, connection fails. > > If you are logged with a Windows login, then things gets trickier. > If server B and server A are in different domains, there is no trust > and things will not work out. But I believe that even if they are > in same domain, it may be difficult, as there are resitrictions with > double hops in Windows authentication. (Not only in SQL Server, but in > Windows in general.) > > The remedy is to setup a login mapping, so that your Windows login maps > to sa on server B. Just make sure that this mapping applies to your > login only, and not everyone on the server! > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Fsb on 14 Jan 2010 15:54 OK, OK so this is what I tried: 1. Log into the source server via SSMS using my windows account (with sysadmin rights) 2. ran the following SQL Statements: EXEC sp_addlinkedserver 'TargetServerName', N'SQL Server' EXEC sp_addlinkedsrvlogin 'TargetServerName', 'FALSE', NULL, 'sa', 'YourSApassword' 3. Both commands report run with no errors. 4. in SSMS I see my new liked server, so i click on it and try to expand it and i get an error that it cant connect. TITLE: Microsoft SQL Server Management Studio ------------------------------ The test connection to the linked server failed. ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Named Pipes Provider: Could not open a connection to SQL Server [5]. OLE DB provider "SQLNCLI10" for linked server "SERVER NAME" returned message "Login timeout expired". OLE DB provider "SQLNCLI10" for linked server "SERVER NAME" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 5) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=5&LinkId=20476 5. So now I try to connect to SSMS with the SA acoount, click on the new linked server i just created and expand it and now it works!!! "Fsb" <Fountainhead7(a)hotmail.com> wrote in message news:eh7UrlVlKHA.6096(a)TK2MSFTNGP02.phx.gbl... Thank you for understanding what I was saying :) Yes, a connection can be made to the "target" server using the sa account and the password specified in the Linked Server( from e.g. SSMS)? Only when I connect to SSMS using the SA account and then create the linked srever using the SA account for the target server. If I use my windows account (with sysadmin rights) to log into SSMS and try to create a liked server, again using the SA account for the traget server it fails! I can connect to any server and versions with no issues. the only difference with the target server is: 1. its on a SAN. 2. Its windows 2008 server OS. I will to try to create a linked server via SQL commands and let you know it it works. "Steen Schlüter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in message news:%23VMl8MhkKHA.6096(a)TK2MSFTNGP02.phx.gbl... From what FSB have said earlier, he is using the "Be made using this security context" in the wizard. This requires a SQL login and password to be entered. When this authentication method is being used, it doesn't matter how you connect to the "source" server. Every connection from here to the remote server is being done by logging on as the SQL account that has been specified (in this case "sa"). I have never experienced that this should work differently depending in how you log on to the source server. As mentioned earlier, I don't know if something has changed in this area in SQL2008 though since I haven't had the change to test this. I'd still like to know though if a connection can be made to the "target" server using the sa account and the password specified in the Linked Server( from e.g. SSMS)? Next step could also be to create the linked server by using the sp_addlinkedserver. If it's between two SQL servers, the syntax is quite simple. The below two lines will create a linked server and add "sa" as SQL login for the linked server EXEC sp_addlinkedserver 'TargetServerName', N'SQL Server' EXEC sp_addlinkedsrvlogin 'TargetServerName', 'FALSE', NULL, 'sa', 'YourSApassword' You can also look up the syntax in BOL to see the other options. The above should work no matter how you connect to the source server - at least it has done for me in many years..:-). -- Regards Steen Schlüter Persson (DK) "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CFC7C376C71BYazorman(a)127.0.0.1... > Fsb (Fountainhead7(a)hotmail.com) writes: >> The issue im finding is that when I connect to the management studio >> using a windows account and then setting up a linked server using the SA >> account it fails. >> >> But If i connect to the management studio using the SA account AND then >> try to setp the a linked server using the SA acoount it works! >> >> So for some reason HOW I connect to the Managment Studio is causing the >> issue... >> >> My widows account is is part of the sysadmin role....SO it should work.... > > Not necessarily. > > Setting up a linked servers includes two steps. First define the linked > server with sp_addlinkedserver, second set up login mapping with > sp_addlinkedsrvlogin. > > It is not clear from your what you have done, but it seems that you > have used the dialogs in SSMS to set up the linked server. I never use > those dialogs, so I can't what you should fill in. But you can use the > Script button to see what they generate. > > But default mapping is that if you are logged in on server A and run > a linked server against server B, server A will attempt to log in > server B with the same credentials. So if you are logged in as sa > on server A, and sa has the same password on server B, you are able to > connet. If the passwords are different, connection fails. > > If you are logged with a Windows login, then things gets trickier. > If server B and server A are in different domains, there is no trust > and things will not work out. But I believe that even if they are > in same domain, it may be difficult, as there are resitrictions with > double hops in Windows authentication. (Not only in SQL Server, but in > Windows in general.) > > The remedy is to setup a login mapping, so that your Windows login maps > to sa on server B. Just make sure that this mapping applies to your > login only, and not everyone on the server! > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 14 Jan 2010 17:30 Fsb (Fountainhead7(a)hotmail.com) writes: > Named Pipes Provider: Could not open a connection to SQL Server [5]. > OLE DB provider "SQLNCLI10" for linked server "SERVER NAME" returned > message "Login timeout expired". > OLE DB provider "SQLNCLI10" for linked server "SERVER NAME" returned > message "A network-related or instance-specific error has occurred while > establishing a connection to SQL Server. Server is not found or not > accessible. Check if instance name is correct and if SQL Server is > configured to allow remote connections. For more information see SQL > Server Books Online.". (Microsoft SQL Server, Error: 5) Error 5 is "Access is denied". It seems that your Windows user is not permitted to access that part of the network, so it does not make contact with the remote server at all. Really what this may be due to, I don't know, but so much is clear that this is a question about configuration of Windows networking and not primarily an SQL Server problem. It probably has something to do with double hops. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Fsb on 20 Jan 2010 10:29 Just to clarify is it still possible that it could be a double hop issue if I am using the destinations SA account when creating the linked server. I�m not using impersonate. Also, difference between this SQL box from all others boxes that I don�t have an issue connecting to are; 1. It�s a virtual machine on a dedicated blade. 2. Windows 2008 server. 3. SQL 2008 Server. 4. The destination server is not using the default port. Should these be areas that could cause an issue? If, so how do you suggest going about excluding each as the cause? Do any of the points I mentioned in previous post already exclude one of the points listed above as not being the issue? Do recap the facts: 1. When I login into the source SQL server box with my own login in, start SSMS using my own login and connecting to the destination using the SA account or (any SQL server account) for the destination it works. These same steps do not work on my local machine. 2. I can connect to the destination server as described above from any other server on my network from my computer. 3. I can connect to the source from the destination as described in step 1 with no issues. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D00EF2AC52A0Yazorman(a)127.0.0.1... > Fsb (Fountainhead7(a)hotmail.com) writes: >> Named Pipes Provider: Could not open a connection to SQL Server [5]. >> OLE DB provider "SQLNCLI10" for linked server "SERVER NAME" returned >> message "Login timeout expired". >> OLE DB provider "SQLNCLI10" for linked server "SERVER NAME" returned >> message "A network-related or instance-specific error has occurred while >> establishing a connection to SQL Server. Server is not found or not >> accessible. Check if instance name is correct and if SQL Server is >> configured to allow remote connections. For more information see SQL >> Server Books Online.". (Microsoft SQL Server, Error: 5) > > Error 5 is "Access is denied". > > It seems that your Windows user is not permitted to access that part of > the network, so it does not make contact with the remote server at all. > Really what this may be due to, I don't know, but so much is clear that > this is a question about configuration of Windows networking and not > primarily an SQL Server problem. It probably has something to do with > double hops. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Network Waits and Read Committed Snapshot Next: Backup file size |