Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: SQL 2k Linked Server Login Mapping for NT Group accounts

    Hi Folks

    SQL2k on NT4 Domain

    I have a UserDB on SvrA - Access to UserDB is Via a Domain\Group and is assigned db_owner role

    UserDB Executes a local SP which in turn Executes a remote SP on SvrB via a Linked Server

    The Linked Server Login Security is via mapping to a remote account which has db_owner on the remote DB.

    I would like to.

    a) stop ALL users on SvrA from being able to use this linked server
    b) tightly restrict permissions of the remote account to the remote db

    Is it possible to MAP above local server login to remote server login as the local login is via a domain group.

    I cannot significantly alter UserDB current Login Security

    I'm also worried that another system/db is utilizing this Link (legally) but I'm not allowed on the production box to monitor it (Hhrummphh - but unfortunately I have the job of providing scripts to tighten security - hence the prefference for mapping local to remote users

    I'm sorry I'm not well versed in SQL Security & this is only a central part of my bigger security job(involving horrible RPC's, dynamic SQL, heteregeounous joins, double hops, delegation etc)

    Any help appreciated

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm 99 5/8% sure that you can find everything you need in the sp_addlinkedsrvlogin documentation. You can either drop the default login, or set it to use NULL for the @rmtuser and @rmtpassword to prevent anyone you don't explicitly authorize to use the linked server. Example B looks like just the ticket for the connection that you want.

    -PatP

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Thanks Pat

    But it's always the 3/8ths percent probability that gets us everytime

    for mapping sp_addlinkedsrvlogin documentation Says
    locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.
    & Access to UserDB is Via a Domain\Group

    so i'm really asking Is it possible to MAP (locallogin) to remote server login as the local login is via a domain group

    example B sows mapping an individual user of the group but I want the entire group mapped

    I can't enable windows group login at other end of link cos I got a double hop (SvrC) n can't enable delegation (NT4 Domain)

    GW
    Last edited by GWilliy; 03-01-08 at 16:45.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, let's call the servers Curly, Moe, and Larry. Bob connects to moe, hops to Larry, then double-hops to Curly as Jane. Which parts are covered by which domains.

    By the way, this thread is now a candidate for how to ask half a question!

    -PatP

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I'm too young to fully understand the Marx Bros quip but yes, maybe I should'nt have tried to ask 3 questions disguised as one.

    Reading my first post I can see I did'nt explain this
    Is it possible to MAP (locallogin) to remote server login as the local login is via a domain group

    example B sows mapping an individual user of the group but I want the entire group mapped
    clearly enough

    But I do know better than asks questions here before reading BOL.

    For Clarity I'm only dealing with
    3 Servers
    1 Domain
    1 Domain Group Login (as locallogin)

    Other than the info already provided I'm at a loss as to how to make this question simpler.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have one domain, and it is an NT4 domain? Ok, the answer to your question just got a whole lot simpler. Jump forward ten years, upgrade to Windows 2003 Server.

    -PatP

  7. #7
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I'm going to assume the answer to my question is No.

    For Info - they've been trying to get everyone on AD for the last 6 mths (3 aborts) prob because of the legacy multiple VPN's / MPLS connections to more than a dozen countrys.

    simple would be for them to let me re-write the TSQL rather than enable them to expand the useage of multiple linked server hops (nasty).

    you gotta remember half the people who post here don't have cart'e blanche on their infrastructure or company wallet.

    sorry it turned crappy - thx anyway

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wait a second... How did we get from three servers in one location to more than a dozen in multiple countries? Did I miss another meeting somehow?

    There are ways to enable multi-hopping in the NT4 environment. Once upon a long ago I used to carry the destructions for doing that with me on a CD, but I haven't needed to remember how for many years, so that information isn't something I keep with me on the road anymore.

    What you are looking for is something equivalent to the modern SETSPN.EXE but that works on an NT4/IP4 domain. There are two DLL files, an EXE, and multiple registry changes to make this happen on the server(s) that need to proxy (in my example Moe and Larry).

    An alternative is to use revert SQL back to sp3a and enable self-impersonation (if that meets your security requirements), or to enter individual linked server logins for each of the group members on each of the SQL Server's that need to allow hops.

    There are more ways to skin a cat than there are cats, but that is no reason to stop trying. I'm inclined to agree with you though... Fixing the SQL is probably a better answer than trying to build a complex, non-standard, and very short lived kludge to support a bad design.

    -PatP

  9. #9
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    missed the meeting - LOL - I don't get invited to meetings anymore since I called their Coders a bunch of Kludgers.

    The 3 SQL Boxes in question won't go Active Directory without everything else.

    Thanks for the post - this has the meat in it i've been looking for - hit the nail on the head.

    we're on latest SP throughout so won't go back to sp3a, I don't know aything about self-impersonation.

    Adding individual logins from the group is too high maintanence (high staff turnover) .

    now i'm fairly certain of the answer to my Q I'll offer them there options.

    1) Change Code Architecture
    2) Continue to allow all local sql box users access via linked server (max restrictions on permissions for mapped remotelogin)
    3) individual windows logins propagated to linked servers (incl hops)
    4) wait for Active Directory n enable delegation
    5) build complex non-standard Kludge with registry hacks on Production Servers.

    I'm halfway through scripting max security for option 2 now anyway - I wuz just looking for alternatives. I can't yet convince them to leave SQLProfiler running on the linked server for a week to see if any other db systems/coders have legally taken advantage of the open linked server security, which I don't know about & my patch will kill instantly.

    I hate leaving bad architecture behind but so often nowadays the previous project code is paid for, working (as far as management can see) & so embedded in stone.

    I'm not into chasing Cat's - The Pay for cat fur is crap

    Thanks for your help Pat

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  10. #10
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Quote Originally Posted by Pat Phelan
    Ok, let's call the servers Curly, Moe, and Larry.
    Quote Originally Posted by GWilly
    I'm too young to fully understand the Marx Bros quip...
    Ouch...

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Please tell me you were joking when you referred to Larry, Moe and Curly as the Marx brothers?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Quote Originally Posted by Thrasymachus
    Please tell me you were joking when you referred to Larry, Moe and Curly as the Marx brothers?
    LOL - Well I said I was too young
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •