| Author |
Topic |
|
wlynnsargent
Starting Member
6 Posts |
 Posted - 2004-08-19 : 10:51:58
|
| Good day!I am trying to set up a linked server between two SQL2k servers. I have created the linked server and set it to use Windows authentication (company standard). I have also created a global group to define access privileges and assigned my user ID to that group.This configuration works fine when I'm logged directly into the server containing the link. However, when I connect to the same server using Enterprise Manager on a remote system (my laptop) and attempt to access the linked server, it reports a 'null' user ID and errors out. I'm assuming this has something to do with the manner in which windows handles the remote login through the group ID. All Enterprise Mgr registrations are set up to use Windows authentication.Any ideas as to whether and how I can get the linked server to recognize the remote EM login would be greatly appreciated.Thanks,-Lynn |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-19 : 14:19:09
|
| We have the same problem. Due to it, we used SQL authentication with the linked servers. This same problem has been talked about here a couple of times, but no one has been able to figure out why it occurs.Tara |
 |
|
|
wlynnsargent
Starting Member
6 Posts |
Posted - 2004-08-20 : 17:28:27
|
Thanks for the input, Tara. Since numerous queries into MSDN Knowledge Base produced nothing, I pretty much figured it was going to turn out to be a "feature." (But it never hurts to check...)  -Lynn S.Gee, it would sure be nice if it did that...maybe in the next revision...or not... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-20 : 17:32:03
|
| I'll post back to this thread if we ever figure it out. So make sure you're subscribed to this thread cuz who knows when the answer will come. Please do the same if you find the answer.Tara |
|
|
|
wlynnsargent
Starting Member
6 Posts |
Posted - 2004-08-20 : 18:16:02
|
| Will do... Thanks again!-Lynn S.Gee, it would sure be nice if it did that...maybe in the next revision...or not... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-21 : 00:35:08
|
| hi, i had the same concern and 'googled' my way to an answer.not sure if this helps, but I've read something about the need to remap logins and remote logins when using linked servers. windows authentication will not work coz the linked server authentication has a different process for doing that. you have to define the remote login.sorry but i forgot the site but try BOL and MS. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-23 : 12:08:33
|
| Yeah but it should work when in the same domain and the account has access to both servers.Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-25 : 01:29:07
|
Hi Tara,But I'm unable to set it up using a windows account on the same domain. Do you have some special pointers? I've tried so many instructions posted and even on BOL but can't seem to set it up.quote:
Originally posted by tduggan
Yeah but it should work when in the same domain and the account has access to both servers.Tara
|
|
|
|
wlynnsargent
Starting Member
6 Posts |
Posted - 2004-08-25 : 01:58:59
|
Jen,As I mentioned in the original post, we have been able to get it to work with the following configuration / limitations:- We are using a global group for authentication. My Windows ID is attached to the group.
- The group ID has to be defined on both servers with appropriate permissions.
- The linked server can only be accessed when I am logged directly onto the server that the link is defined on; it returns an error if I try to access it from any other workstation. (The subject of this thread...)
Obviously it is difficult to determine whether there might be any other configuration issues standing in your way, but this setup does seem to work when I'm going in through EM. I'm almost ready to try executing a stored proc on the linked server through a scheduled job. It'll be interesting to see whether the job will process if I'm not directly logged in...-Lynn quote:
Originally posted by jen
But I'm unable to set it up using a windows account on the same domain. Do you have some special pointers? I've tried so many instructions posted and even on BOL but can't seem to set it up.
-Lynn S.Gee, it would sure be nice if it did that...maybe in the next revision...or not... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 03:06:53
|
| Yep, this issue is a right PITA. You can "work around" it using SQL authentication.Also, according to some of the MS knowledge base articles, it should be possible to allow the login delegation depending on your setup, but you have to be on Active Directory and have various boxes ticked and not ticked. ( See Kerberos / Security Account delegation in BOL ) But I think that the machines here are set up that way and it still doesn't work. :|Strangely, this was marked as a bug in SQL 7, but all references to it seemed to disappear with 2000.-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-25 : 03:18:47
|
Hi Moo,If i create a linked server using sql authentication, will other users be able to use the link? I only want to restrict the linked server capability for the jobs I'll be creating.Lynn, please update if you were successfull in your endeavor with regards to the job  |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 03:30:46
|
| HiCreate the linked server as normal, then you need to alias NT logins to SQL logins on the remote box, through the "security" tab of the linked server. Unfortunately it means that (on the remote side) you have to create the SQL login(s), which you or your security team may be unwilling to do. Anyone can use the link whose account has a login mapping. (Likewise noone without a mapping can use the link).-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-25 : 03:47:59
|
| ah, so what you're saying is that unless i map the logins on both the servers to use this link, they will not be able to use the link?or is it automatic that anyone who has both access to serverA and serverB can use the link? |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 03:53:36
|
| Somewhat, yes.The default setting for a linked server is that connections are made without security context.You can use the security tab to override that for specific logins, and choose what happens to the rest, eg log on with existing credentials, or not log on at all. If you only wanted one (nt) logon to pass through to the linked server as the sa account, you would fill in the boxes as -local login - yourdomain\yourloginImpersonate - leave untickedremote user - saremote password - sa passwordTick the radio button next to "not be made".Of course, the problem described in this thread does a good job of ensuring that most connections are denied anyway ;)-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-25 : 04:29:33
|
| thanks again moo, it worked as expected...all good things come in three, i hope my concern regarding cluster will be answered today also... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-08-25 : 06:56:24
|
quote:
Originally from EdVassie on SqlServerCentralMy understanding of the theory is1) You need to read what BOL says about this2) You need to be running W2K or above & SQL2K3) You need to talk to your security guys and NT admin guys about allowing 4) 5) & 6)4) You need to register your servers with AD5) You need to grant the Kerberos right for 'Allow account delegation' to all 'sending' servers.6) You need to grant the Kerberos right 'Accept account delegation' to all 'receiving' servers If you have one master server that requests data from other servers, then the master is the sender and the others are receivers. If all servers request data from each other then they are all senders and receivers. Hope it works! All information provided is a personal opinion that may not match reality.All information provided is a personal opinion that may not match reality.
The above is from:http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=17546where there was a similar threadWe have had the same problems here :-(Not solved hitherto, but after this read We might have a go again./rockmoose |
|
|
|
wlynnsargent
Starting Member
6 Posts |
Posted - 2004-08-25 : 17:28:32
|
| Well, it appears as though my test was successful! I set up a scheduled job on my receiving server that fired off a local stored proc, which in turn executed a SP on the linked server, received the resulting data, and stored the results in a local table. All this was done using windows group authentication, and with my windows account logged off of the server. (I should note, however, that the owner of the scheduled job must be a member of the privileged group.)I still can't run any of the linked server queries/stored procs directly from my local machine, but I have a feeling that may just be the nature of things. (Let's hear it for NetOp!)Thanks for all your input on this subject!-Lynn S.Gee, it would sure be nice if it did that...maybe in the next revision...or not... |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-26 : 05:20:34
|
quote:
Originally posted by wlynnsargentI still can't run any of the linked server queries/stored procs directly from my local machine, but I have a feeling that may just be the nature of things. (Let's hear it for NetOp!)
To be fair, this was always the crux of the problem, running a job from the machine that is accessing the linked server directly has not been an issue. (Unless others are having bigger problems.)-------Moo. :) |
|
|
|
|
Linked server using group authentication