Linked Server and Windows Group Local Login

  • I have a little problem. I know what the problem is, but I can't find any information about it.

    I have a windows group login to my SQL2K box. Let's call it DeptA. This DeptA is a Windows Group in AD. They have permission to run a few stored procedures in a DB. A few of these stored procedures need to connect to a different server, so a linked server was set up.

    I know that I could set up AD to allow trusted connections and delegation, but my security and network guys don't like that idea. Instead, I've set up a SQL account, and I have DeptA mapped to the SQL account on the linked server.

    The problem is, it doesn't work. If I use DOMAIN\Bob, or DOMAIN\Dave, then Bob and Dave in DeptA can run the stored procedures. If I create a mapping for DOMAIN\DeptA, everyone who accesses SQL Server via their group membership of DeptA gets an error.

    I'm not worried about the error. I know that I get the error because the linked server connection isn't able to see that the user is a part of the windows group. My problem is that I can't find any documentation on this phenomena, and I also can't find a way round it short of creating a single login account for every new user (which is far too much work if I can avoid it).

     

    Anybody seen/fixed this issue? Or even some documentation somewhere on the problem?

  • Ian,

    I don't have any documentation on this but we worked through this same issue with Microsoft.  We tried to map a windows AD group to a SQL login but that does not work.  You have to map an individual windows login to a SQL Login.  We ended up creating one SQL Admin login and mapped our windows admin logins to that login.  The other logins are using a SQL login that has read only access.  There really was an easier or nicer way to do it.  Security account delegation only works if you have the right environment (everything is in Windows 2000).  Let me know if you have any other questions, let me know and I will try to answer them.

    HTH,

    John

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply