Linked server - from 2008 to a 2005 box.
in [Server Configuration]
|
From: Fsb on 8 Jan 2010 11:28 My appologies. Let me try to explain it again in a bit more detail. From my local machine I connect to a SQL 2008 Server from the management Studio. When connecting I use my windows credentials. My windows account is part of the sysadmin SQL Server 2008 role. OK, so now I'm logged into the box under my windows account and try to create a linked server to a 2005 box. So I right click on the "create new linked server" and under security I select the option to connect to the box via "be made using this security context:" and use the SA account for the 2005 box. This does not work for some reason it says it times out trying to connect. Now if I do the same on the actual 2008 box (under my own account) it works fine or If I connect to the SQL 2008 box from my local machine from management studio using the SQL 2008 server SA account and then try to the steps above under SQL Server 2008 SA it works fine. The other odd thing is i can connect to other 2005 boxes using the same steps above and it works fine! "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in message news:eqAKfiHkKHA.1864(a)TK2MSFTNGP05.phx.gbl... > I'm not wuite sure that I get what you mean. Are you connecting using a > SQL account or a Windows account? Using a SQL account, you'll have to > specify this account when setting up the linked server - and on both > servers. If you want to connect to the linked server with you windows > account, you'll have to setup delegation and SPN etc. as outlined in the > links I sent you. > Personally I've in most cases used SQL authentication when using linked > servers because that's the easiest to get working - and then you don't > have to set any SPN's or delegation etc. > > I have to say though that I haven't setup linked server on SQL2008 yet, so > I can't say if something has changed here. > > > -- > Regards > Steen Schl�ter Persson (DK) > > > "Fsb" <Fountainhead7(a)hotmail.com> wrote in message > news:e%23h2saHkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >> Why could i not just select the option to "be made using the security >> context" >> >> and use the sa for the server im trying to connect to? >> >> I can conect the using the sa accout the other way around (i.e 2005to >> 2008) but it does not work from 2008 to 2005. >> >> >> "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in message >> news:O5EVOn9jKHA.5568(a)TK2MSFTNGP02.phx.gbl... >>>I don't think you problem is odd..:-). >>> >>> You'll have to tell the linked server to use windows authentication. >>> You'll have to set up delegation between the servers and that's >>> something you'll have to do in your AD. >>> >>> Try to look up "Configuring Linked Servers for delegation" in BOL - that >>> will get you started. >>> >>> http://msdn.microsoft.com/en-us/library/ms189580(SQL.90).aspx (2005) >>> >>> http://msdn.microsoft.com/en-us/library/ms189580.aspx (2008) >>> >>> -- >>> Regards >>> Steen Schl�ter Persson (DK) >>> >>> >>> "Fsb" <Fountainhead7(a)hotmail.com> wrote in message >>> news:O3aKYm8jKHA.5076(a)TK2MSFTNGP05.phx.gbl... >>>>I have an odd problem. >>>> >>>> I created a linked server from a 2008 box to a 2005 box with the sa >>>> account. >>>> >>>> I have a windows account that is part of the sysadmin role but cant I >>>> cant query or expand the linked server to see alll it tables. but under >>>> the sa account I can. >>>> >>>> what am i missing? >>>> >>> >>> >> >> > >
From: Steen Schl�ter Persson on 8 Jan 2010 15:42 Ok - so there are no windows authentication involced in the Linked Server setup. The SA account you are using to connect with, has to exists on the target server with the password you specify. Maybe I'm just missing something, but from your explanation it doesn't look like you've ever tried to connect to the SQL2005 instance using the SA account and password you specify in the Linked Server setup? This would be a good test to verify that you can connect with that SA password. -- Regards Steen Schl�ter Persson (DK) "Fsb" <Fountainhead7(a)hotmail.com> wrote in message news:upcbV%23HkKHA.1824(a)TK2MSFTNGP04.phx.gbl... > My appologies. > > Let me try to explain it again in a bit more detail. > > From my local machine I connect to a SQL 2008 Server from the management > Studio. When connecting I use my windows credentials. My windows account > is part of the sysadmin SQL Server 2008 role. > > OK, so now I'm logged into the box under my windows account and try to > create a linked server to a 2005 box. So I right click on the "create new > linked server" and under security I select the option to connect to the > box via "be made using this security context:" and use the SA account for > the 2005 box. > > This does not work for some reason it says it times out trying to connect. > > Now if I do the same on the actual 2008 box (under my own account) it > works fine or If I connect to the SQL 2008 box from my local machine from > management studio using the SQL 2008 server SA account and then try to the > steps above under SQL Server 2008 SA it works fine. > > The other odd thing is i can connect to other 2005 boxes using the same > steps above and it works fine! > > "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in message > news:eqAKfiHkKHA.1864(a)TK2MSFTNGP05.phx.gbl... >> I'm not wuite sure that I get what you mean. Are you connecting using a >> SQL account or a Windows account? Using a SQL account, you'll have to >> specify this account when setting up the linked server - and on both >> servers. If you want to connect to the linked server with you windows >> account, you'll have to setup delegation and SPN etc. as outlined in the >> links I sent you. >> Personally I've in most cases used SQL authentication when using linked >> servers because that's the easiest to get working - and then you don't >> have to set any SPN's or delegation etc. >> >> I have to say though that I haven't setup linked server on SQL2008 yet, >> so I can't say if something has changed here. >> >> >> -- >> Regards >> Steen Schl�ter Persson (DK) >> >> >> "Fsb" <Fountainhead7(a)hotmail.com> wrote in message >> news:e%23h2saHkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>> Why could i not just select the option to "be made using the security >>> context" >>> >>> and use the sa for the server im trying to connect to? >>> >>> I can conect the using the sa accout the other way around (i.e 2005to >>> 2008) but it does not work from 2008 to 2005. >>> >>> >>> "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in >>> message news:O5EVOn9jKHA.5568(a)TK2MSFTNGP02.phx.gbl... >>>>I don't think you problem is odd..:-). >>>> >>>> You'll have to tell the linked server to use windows authentication. >>>> You'll have to set up delegation between the servers and that's >>>> something you'll have to do in your AD. >>>> >>>> Try to look up "Configuring Linked Servers for delegation" in BOL - >>>> that will get you started. >>>> >>>> http://msdn.microsoft.com/en-us/library/ms189580(SQL.90).aspx (2005) >>>> >>>> http://msdn.microsoft.com/en-us/library/ms189580.aspx (2008) >>>> >>>> -- >>>> Regards >>>> Steen Schl�ter Persson (DK) >>>> >>>> >>>> "Fsb" <Fountainhead7(a)hotmail.com> wrote in message >>>> news:O3aKYm8jKHA.5076(a)TK2MSFTNGP05.phx.gbl... >>>>>I have an odd problem. >>>>> >>>>> I created a linked server from a 2008 box to a 2005 box with the sa >>>>> account. >>>>> >>>>> I have a windows account that is part of the sysadmin role but cant I >>>>> cant query or expand the linked server to see alll it tables. but >>>>> under the sa account I can. >>>>> >>>>> what am i missing? >>>>> >>>> >>>> >>> >>> >> >> > >
From: Fsb on 9 Jan 2010 18:45 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.... "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in message news:OD2KJMKkKHA.4672(a)TK2MSFTNGP06.phx.gbl... > Ok - so there are no windows authentication involced in the Linked Server > setup. > The SA account you are using to connect with, has to exists on the target > server with the password you specify. > Maybe I'm just missing something, but from your explanation it doesn't > look like you've ever tried to connect to the SQL2005 instance using the > SA account and password you specify in the Linked Server setup? This would > be a good test to verify that you can connect with that SA password. > > -- > Regards > Steen Schl�ter Persson (DK) > > "Fsb" <Fountainhead7(a)hotmail.com> wrote in message > news:upcbV%23HkKHA.1824(a)TK2MSFTNGP04.phx.gbl... >> My appologies. >> >> Let me try to explain it again in a bit more detail. >> >> From my local machine I connect to a SQL 2008 Server from the management >> Studio. When connecting I use my windows credentials. My windows account >> is part of the sysadmin SQL Server 2008 role. >> >> OK, so now I'm logged into the box under my windows account and try to >> create a linked server to a 2005 box. So I right click on the "create new >> linked server" and under security I select the option to connect to the >> box via "be made using this security context:" and use the SA account for >> the 2005 box. >> >> This does not work for some reason it says it times out trying to >> connect. >> >> Now if I do the same on the actual 2008 box (under my own account) it >> works fine or If I connect to the SQL 2008 box from my local machine from >> management studio using the SQL 2008 server SA account and then try to >> the steps above under SQL Server 2008 SA it works fine. >> >> The other odd thing is i can connect to other 2005 boxes using the same >> steps above and it works fine! >> >> "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in message >> news:eqAKfiHkKHA.1864(a)TK2MSFTNGP05.phx.gbl... >>> I'm not wuite sure that I get what you mean. Are you connecting using a >>> SQL account or a Windows account? Using a SQL account, you'll have to >>> specify this account when setting up the linked server - and on both >>> servers. If you want to connect to the linked server with you windows >>> account, you'll have to setup delegation and SPN etc. as outlined in the >>> links I sent you. >>> Personally I've in most cases used SQL authentication when using linked >>> servers because that's the easiest to get working - and then you don't >>> have to set any SPN's or delegation etc. >>> >>> I have to say though that I haven't setup linked server on SQL2008 yet, >>> so I can't say if something has changed here. >>> >>> >>> -- >>> Regards >>> Steen Schl�ter Persson (DK) >>> >>> >>> "Fsb" <Fountainhead7(a)hotmail.com> wrote in message >>> news:e%23h2saHkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>>> Why could i not just select the option to "be made using the security >>>> context" >>>> >>>> and use the sa for the server im trying to connect to? >>>> >>>> I can conect the using the sa accout the other way around (i.e 2005to >>>> 2008) but it does not work from 2008 to 2005. >>>> >>>> >>>> "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in >>>> message news:O5EVOn9jKHA.5568(a)TK2MSFTNGP02.phx.gbl... >>>>>I don't think you problem is odd..:-). >>>>> >>>>> You'll have to tell the linked server to use windows authentication. >>>>> You'll have to set up delegation between the servers and that's >>>>> something you'll have to do in your AD. >>>>> >>>>> Try to look up "Configuring Linked Servers for delegation" in BOL - >>>>> that will get you started. >>>>> >>>>> http://msdn.microsoft.com/en-us/library/ms189580(SQL.90).aspx (2005) >>>>> >>>>> http://msdn.microsoft.com/en-us/library/ms189580.aspx (2008) >>>>> >>>>> -- >>>>> Regards >>>>> Steen Schl�ter Persson (DK) >>>>> >>>>> >>>>> "Fsb" <Fountainhead7(a)hotmail.com> wrote in message >>>>> news:O3aKYm8jKHA.5076(a)TK2MSFTNGP05.phx.gbl... >>>>>>I have an odd problem. >>>>>> >>>>>> I created a linked server from a 2008 box to a 2005 box with the sa >>>>>> account. >>>>>> >>>>>> I have a windows account that is part of the sysadmin role but cant I >>>>>> cant query or expand the linked server to see alll it tables. but >>>>>> under the sa account I can. >>>>>> >>>>>> what am i missing? >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
From: Uri Dimant on 10 Jan 2010 03:24 Fsb Wow , is it russian intelligence servive :-)))) Yes, it should work with Windows Account as you described. What is the diff between this specific machine and other SQL Server 2005 boxes? Any records in the ERROR.LOG? "Fsb" <Fountainhead7(a)hotmail.com> wrote in message news:O8K0TXYkKHA.4912(a)TK2MSFTNGP02.phx.gbl... > 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.... > > > > > "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in message > news:OD2KJMKkKHA.4672(a)TK2MSFTNGP06.phx.gbl... >> Ok - so there are no windows authentication involced in the Linked Server >> setup. >> The SA account you are using to connect with, has to exists on the target >> server with the password you specify. >> Maybe I'm just missing something, but from your explanation it doesn't >> look like you've ever tried to connect to the SQL2005 instance using the >> SA account and password you specify in the Linked Server setup? This >> would be a good test to verify that you can connect with that SA >> password. >> >> -- >> Regards >> Steen Schl�ter Persson (DK) >> >> "Fsb" <Fountainhead7(a)hotmail.com> wrote in message >> news:upcbV%23HkKHA.1824(a)TK2MSFTNGP04.phx.gbl... >>> My appologies. >>> >>> Let me try to explain it again in a bit more detail. >>> >>> From my local machine I connect to a SQL 2008 Server from the management >>> Studio. When connecting I use my windows credentials. My windows >>> account is part of the sysadmin SQL Server 2008 role. >>> >>> OK, so now I'm logged into the box under my windows account and try to >>> create a linked server to a 2005 box. So I right click on the "create >>> new linked server" and under security I select the option to connect to >>> the box via "be made using this security context:" and use the SA >>> account for the 2005 box. >>> >>> This does not work for some reason it says it times out trying to >>> connect. >>> >>> Now if I do the same on the actual 2008 box (under my own account) it >>> works fine or If I connect to the SQL 2008 box from my local machine >>> from management studio using the SQL 2008 server SA account and then try >>> to the steps above under SQL Server 2008 SA it works fine. >>> >>> The other odd thing is i can connect to other 2005 boxes using the same >>> steps above and it works fine! >>> >>> "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in >>> message news:eqAKfiHkKHA.1864(a)TK2MSFTNGP05.phx.gbl... >>>> I'm not wuite sure that I get what you mean. Are you connecting using a >>>> SQL account or a Windows account? Using a SQL account, you'll have to >>>> specify this account when setting up the linked server - and on both >>>> servers. If you want to connect to the linked server with you windows >>>> account, you'll have to setup delegation and SPN etc. as outlined in >>>> the links I sent you. >>>> Personally I've in most cases used SQL authentication when using linked >>>> servers because that's the easiest to get working - and then you don't >>>> have to set any SPN's or delegation etc. >>>> >>>> I have to say though that I haven't setup linked server on SQL2008 yet, >>>> so I can't say if something has changed here. >>>> >>>> >>>> -- >>>> Regards >>>> Steen Schl�ter Persson (DK) >>>> >>>> >>>> "Fsb" <Fountainhead7(a)hotmail.com> wrote in message >>>> news:e%23h2saHkKHA.1652(a)TK2MSFTNGP05.phx.gbl... >>>>> Why could i not just select the option to "be made using the security >>>>> context" >>>>> >>>>> and use the sa for the server im trying to connect to? >>>>> >>>>> I can conect the using the sa accout the other way around (i.e 2005to >>>>> 2008) but it does not work from 2008 to 2005. >>>>> >>>>> >>>>> "Steen Schl�ter Persson" <steen(a)REMOVETHISasavaenget.dk> wrote in >>>>> message news:O5EVOn9jKHA.5568(a)TK2MSFTNGP02.phx.gbl... >>>>>>I don't think you problem is odd..:-). >>>>>> >>>>>> You'll have to tell the linked server to use windows authentication. >>>>>> You'll have to set up delegation between the servers and that's >>>>>> something you'll have to do in your AD. >>>>>> >>>>>> Try to look up "Configuring Linked Servers for delegation" in BOL - >>>>>> that will get you started. >>>>>> >>>>>> http://msdn.microsoft.com/en-us/library/ms189580(SQL.90).aspx (2005) >>>>>> >>>>>> http://msdn.microsoft.com/en-us/library/ms189580.aspx (2008) >>>>>> >>>>>> -- >>>>>> Regards >>>>>> Steen Schl�ter Persson (DK) >>>>>> >>>>>> >>>>>> "Fsb" <Fountainhead7(a)hotmail.com> wrote in message >>>>>> news:O3aKYm8jKHA.5076(a)TK2MSFTNGP05.phx.gbl... >>>>>>>I have an odd problem. >>>>>>> >>>>>>> I created a linked server from a 2008 box to a 2005 box with the sa >>>>>>> account. >>>>>>> >>>>>>> I have a windows account that is part of the sysadmin role but cant >>>>>>> I cant query or expand the linked server to see alll it tables. but >>>>>>> under the sa account I can. >>>>>>> >>>>>>> what am i missing? >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
From: Erland Sommarskog on 10 Jan 2010 06:12 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
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Network Waits and Read Committed Snapshot Next: Backup file size |