(Riga) System Administrator should be able to access DB2 linked server?

  • JMI

    SSChampion

    Points: 11831

    DBA made me System Administrator on SQL Server 2000 box.

    There is a linked server to DB2 database registered on that box.

    It's registered with a specific DB2 account.

    When I query that linked server I get an error. When DBA connects using her account and SQL Server Authentication she can see query results OK.

    I thought System Administrator has access to anything including

    linked servers? Am I wrong?

  • Johan Bijnens

    SSC Guru

    Points: 134306

    Well... there is this little caveot with linked servers. They have their own credential mapping.

    Have a look at the linked server security tab.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • JMI

    SSChampion

    Points: 11831

    I checked Security Tab for linked server.

    This is selected:

    "Connections will be made using this security context":

    Remote login: niadetl

    Does it mean if I'm not connected to SQL server as "niadetl"

    my connection to a linked server will fail

    even if I'm a member of SQL Server "System Administrator" role?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721381

    I'd think that everyone would connect with those credentials. What else is set on that tab?

  • JMI

    SSChampion

    Points: 11831

    Nothing else.

    No Local Login/Impersonate/Remote login.

    Nothing.

    Just "...be made using this security context..." is checked.

  • JMI

    SSChampion

    Points: 11831

    And one more question.

    SQL Server job in step 1 is calling stored procedure that

    accesses DB2 and SQL Server linked servers

    and that job is running OK.

    I checked SQL Server Agent Connection Tab Properties

    in Enterprise Manager and it shows "SQL Server connection: Use Windows Authentication".

    Then I went to Services console and checked SQLERVERAGENT

    Properties / Log On tab. "Local System account" is checked.

    I'm not quite sure what account Agent is running under but in any case it's not "niadetl" account.

    So I'm confused now.

    According to Steve Jones only the account specified

    in Security Tab of Linked Server properties can access

    linked server catalogs and in my case it's "naidetl" account.

    Then how come SQL Server Agent successfully runs the job that

    is accessing the linked server registered with "niadetl" account?

  • Johan Bijnens

    SSC Guru

    Points: 134306

    this is a long shot...

    but are u member of the (local) windows group of DB2 users ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • mark johnson-159789

    SSC-Addicted

    Points: 416

    I think Alzdba is on the correct path. Have a DB2 dba double check the accounts involved for access to the DB2 data. I would start with niedtl the account, then possilbly the service account. I agree with Steve Jones in that the linked server should be using the niedtl account. The little bit I messed with DB2 it seemed a bit fussy for linked servers.

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • mark johnson-159789

    SSC-Addicted

    Points: 416

    Is it possible that the password on the linked server is out of whack? Does not seem like dba should be able to use the linked server as is as well. It should not be passing other credentials to the DB2 database other than the niedetl account, so unless the dba is passing something else in during the connection.

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • mark johnson-159789

    SSC-Addicted

    Points: 416

    Oh, what is the real error that you are seeing?

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • JMI

    SSChampion

    Points: 11831

    Here is the solution.

    I created a SQL Server account on ServerA

    as SYSADMIN. I connect to ServerA in QA using this account.

    Now all my distributed queries to DB2 linked server (ServerB) work.

    Keep in mind my Windows account is SQL SYSADMIN as well.

    Apparently it happened to one of the developers here already.

    It looks like if you register linked server under a specific

    account you can not continue working under Windows Authentication

    and expect that you can access that linked server successfully.

    That's what I got out of this situation.

    I might be wrong.

  • mark johnson-159789

    SSC-Addicted

    Points: 416

    We use specific accounts on linked servers and windows authentication, does the sysadmin account on server A have access as an admistrator on the server that gives it access to db2? If neidetl does not have access to the specific databases and tables it should fail even if your sysadmin account has priviliges to db2. I think we are missing part of the picture. I'm glad that your linked server is working, but have concerns that you might actually be granting more access than intended to your DB2 data:).

    Good luck;

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • JMI

    SSChampion

    Points: 11831

    For DB2 linked server we use "app" account with limited rights.

    I'm not sure but I think it has only READ permissions.

    This account works. We tested it a lot of times from DB2 client

    installed on ServerA (SQL Server 2000).

    As I mentioned earlier Linked Server with that account works

    but only when you use SQL Server authentication to connect to ServerA.

    The key is that with Windows Authentication we aways get errors

    when trying to access DB2 linked server.

    With SQL Server Authentication it works and you don't need

    DB2 account to be SYSADMIN.

    SQL Server Job that is accessing DB2 linked server works fine.

    But under which account it's executing T-SQL distributed queries is still

    a mystery for me..

  • mark johnson-159789

    SSC-Addicted

    Points: 416

    Sounds like the real error may revolve around not having sql server access to the linked server itself and not the access back to DB2, maybe.

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • JMI

    SSChampion

    Points: 11831

    Sorry. I'm not following you Mark.

Viewing 15 posts - 1 through 15 (of 17 total)

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