October 8, 2002 at 3:59 am
I have a SP on a SQL 7 server (server A) which calls and
executes a SP on another server running SQL 2000 (server
B).
On server A I have a login that owns the database.
On Server B I have the same login and the login is dbo in
the database where it tries to execute the SP.
I use linked servers and I have mapped the logins.
When I run everything in Query Analyser everything
executes the way it is supposed to do but when I execue
the SP in server A from a scheduled job I get an error
message:
Msg 2, Sev 16: Access denied. [SQLSTATE 42000]
Can someone help me and tell me what I do wrong?
.
October 8, 2002 at 4:53 am
How did you define you linked server?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 8, 2002 at 5:48 am
I used the following two procedures for the linked server and the user
EXEC sp_addlinkedserver
'BLGN185',
'SQL Server'
sp_addlinkedsrvlogin
@rmtsrvname = 'BLGN185'
, @useself = 'false'
, @locallogin = 'OPUSBatch'
, @rmtuser = 'OPUSBatch'
, @rmtpassword = 'password'
October 8, 2002 at 7:37 am
You need to review what logins have you mapped from Server A to Server B. Is the SQL Server Agent login on Server A mapped? I suspect that the you need to map the SQL Server Agent login on Server A to a user on Server B.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 8, 2002 at 8:18 am
I have now added the SQL Server agent login, and almost every other login that I can think of, to the mapped logins. But still the same error, Access Denied.
October 8, 2002 at 8:36 am
Run sp_helplinkedsrvlogin and review the output. Verify that your server A login, maps to the correct Remote Server login.
You might try to logon to Server A using your SQL Agent login, and try to run the proc, and see if you also get "Access Denied".
Also you might try mapping your Server A SQL Agent login, to SA on Server B, to see what happens.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 8, 2002 at 9:20 am
It worked when I mapped my logins to sa.
Thanks a lot!!
October 8, 2002 at 9:44 am
This is great it worked.
This leads me to think (a very dangerous thing to do) that the login you originally mapped your SQL Server Agent account to, might not have add the necessary permissions to perform what you wanted to do on Server B.
Using SA basically give your SQL Agent Account, god like authority on Server B.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 8, 2002 at 5:29 pm
Yes, I would play a bit with SQL Accounts on Server B because with SA you have now opened where just about anyone can get thru depending on if you did a generic mapping or if you set only specific accounts access to the other server that way. SA is a big security risk.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 10, 2002 at 3:53 am
I have discovered something strange, or at least something that I do not understand.
I mapped my login that I used for running the SP to sa everything worked just fine. The first night that is.
This morning when I got here the job had failed with the error message Access denied.
Yesterday when I had mapped OPUSBatch to sa on the other server and when I in Query Analyzer, logged on as Opus Batch, ran the query
select * from blgn185.opus.dbo.anvandare
I got the result that I wanted
When I did the same thing this morning I got the error message Acces Denied in Query Analyzer.
I then opened a window where I logged on as sa and ran the same query. There I got the result that I wanted. I then returned to my other window and then I got a result from that query to.
And when I rescheduled my batch and ran it, it succeeded.
Now I have waited for two hours and now I am back to Access denied again and I haven't touched anything. I ran the query in QA as sa and after that everything is OK again.
I can´t understand this.
October 10, 2002 at 4:23 am
Can you do a print screen on your properties in EM for the linked server? All three tabs and post them somewhere we can look at. Also, when the problem is occurring have you tried using Profiler (from the other machine) to verify weather or not the server that is using the link is submitting the request?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 10, 2002 at 8:17 am
I think the profiler test is a good idea. Also do you see any errors in log? Also you might try to determine if you are getting logged in to Server B, and then getting accessed denied. You might be able to do this by monitoring Server B with profiler.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 14, 2002 at 3:22 am
I had to let this be for some days but when I returned to the problem today everything was the same as last week, it hadn´t fixed itself during the weekend. 🙂
When I run the query (as myself) from QA and have a profiler running I see nothing in server B. I see the query in server A but no other messages.
(I have my user id mapped to sa on the other server)
When I run the query as sa, I get an answer and I see the query in the profiler for server B and also in server A. In server B the NT user name is SQLService and login name is sa. In server A the NTuser name is my user id and the login name is empty.
When I then run the query as myself, I get the answer that I want, and the information in profiler is exactly the same as when I ran the query as sa.
October 14, 2002 at 12:52 pm
Seems like it has to be configuration related. Try dropping the link and recreating. Also, what Service Pack level is your SQL 7 server, I test SP4 and could not duplicate, could be a known issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply