June 16, 2008 at 7:34 am
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?
June 16, 2008 at 7:59 am
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
Learn to play, play to learn !
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 but most of the time this is me
June 16, 2008 at 1:07 pm
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?
June 16, 2008 at 1:20 pm
I'd think that everyone would connect with those credentials. What else is set on that tab?
June 16, 2008 at 2:39 pm
Nothing else.
No Local Login/Impersonate/Remote login.
Nothing.
Just "...be made using this security context..." is checked.
June 17, 2008 at 7:07 am
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?
June 17, 2008 at 10:57 am
this is a long shot...
but are u member of the (local) windows group of DB2 users ?
Johan
Learn to play, play to learn !
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 but most of the time this is me
June 17, 2008 at 11:18 am
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
June 17, 2008 at 11:22 am
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
June 17, 2008 at 11:24 am
Oh, what is the real error that you are seeing?
Mark Johnson
MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP
June 18, 2008 at 7:15 am
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.
June 18, 2008 at 9:17 am
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
June 18, 2008 at 10:16 am
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..
June 18, 2008 at 10:52 am
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
June 18, 2008 at 10:59 am
Sorry. I'm not following you Mark.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply