February 17, 2009 at 10:32 pm
Hi all.
I have an app running on users' local machines (don't laugh, it's Access 2003), and I make many calls to the SQL Server to perform simple SELECT, INSERT, UPDATE statements and calls to stored procedures through ADO from the local machines.
We are running in Windows Authentication Mode only, and the other day, I needed to create a new process which calls a stored procedure from the Access App, which subsequently launches a job, using sp_start_Job.
Naturally, I have pretty complete access to the database, and when I made the ADO Connection using my windows login credentials, I could execute the job. However, most of the normal users of the app don't have that sort of access, and do not have SQLAgentUser rights, and thus they can execute the SP, but cannot subsequently launch the job from within the SP.
We have one default user setup as a windows login who has broader access, and I changed my ADO string to use this user to make the call to the SQL Server, so that the permission level would be such that the users were able to launch the job within the SP when calling as this particular user.
However, the job still fails to launch when logged into the local machine as one of them but runs fine logged in as me.
From the testing I've done, I'm about 99% certain that the SQL Server in Windows Authentication Mode simply ignores any explicit ADO string passed to it from a local machine on the network, and defaults the call to use the login of whoever is logged into the local machine. When I tested the same piece of code on our test server which is in mixed mode, I could execute the whole thing just fine, logged in as a normal user, using the specially setup Windows user in my ADO string.
When not in mixed mode, I could not.
Does anyone know if this is the case.. that if you make a call to the SQL Server from a local machine when in Windows Authentication Mode, the SQL Server will only accept the credentials of the person logged into the calling computer, and that we would have to be running in mixed mode to pass an explicit user in the ADO string?
February 17, 2009 at 10:42 pm
With windows authentication you don’t pass your credentials at all. SQL Server gets you credentials from the OS. In order to solve your problem you can try to specify when you create the stored procedure that it will always run as if you activated it regardless of the real person that ran it. For more details check BOL for create procedure statement and read about EXECUTE AS.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 17, 2009 at 10:58 pm
Thanks for the verification.
So as I suspected, the SQL Server is always using the credentials of the login from the computer that sent the processing request when in Windows Authentication Mode, and there is no way to force it to process as a specific user.
I have tried using EXECUTE AS, and it fails just the same, as I don't think the calling user has permissions then to run the SP at a permission level that is higher than they are.
I suspect we'll have to switch mixed mode back on in order to achieve what we want.
March 6, 2009 at 10:50 am
If you are trying to use the SQL Server Agent to run the job, I believe it doesn't run under your account. The job runs under the account that you have chosen for you SQL Server Agent to run under. So, you might want to look at what account you SQL Server Agent is using.
March 6, 2009 at 2:03 pm
If you are trying to use the SQL Server Agent to run the job, I believe it doesn't run under your account. The job runs under the account that you have chosen for you SQL Server Agent to run under. So, you might want to look at what account you SQL Server Agent is using.
That is correct authentication is not relevant when you run the code manually it runs in the context of that user but when it is a job it runs in the context of the Agent. So you need to make sure that user is of Admin level in SQL Server and on the network level if Access is located on the network then create a proxy of that account to run the Agent. The reason the Agent is installed by the SQL Server 2005 and above installer with the limited network service account not enough to run jobs on the network.
Kind regards,
Gift Peddie
March 8, 2009 at 4:43 pm
Um... That's exactly the problem in the first place. The job has to be run under a user that has SQLAgent credentials.
When called from the stored procedure in question, I do believe you are all right in saying that the called job tries to run as whoever the user is who created that job, not as the calling Windows Authentication user.
However, it's the same problem you would get with using EXECUTE AS in the stored procedure. The user trying to execute the job has higher permissions than the calling Windows Authentication user who called the SP, and therefore the job fails to execute because they do not have sufficient permissions to prompt the user who created the job to run tht job.
I can't see that there is a way around this, unless mixed mode is switched on so that the calling user can be someone with sufficient SQLAgent credentials to run such a job.
The easiest way would be if it were possible somehow to use EXECUTE AS to execute the SP at a higher level. But again, since the EXECUTE AS user would have higher permission levels than the Windows Authentication user calling the SP, the SP would then fail to be able to execute. Same problem, different point in the chain of events.
Unless someone has a specific idea of how to do this without switching mixed mode on, or giving the Windows Authentication Users higher access, I just can't see how it's possible.
For now, I've worked around it, but have had to basically can the idea of calling the refresh in SQL from Access. I'm now simply shutting down any open user front-ends from a timer in Access between 2:30 and 3am, and then letting the full refresh run from a scheduled job on the SQL Server.
It means everyone gets a clean refresh every night, but that they can't refresh their copy at a moment's notice whenever they like. If anything corrupts during the day, we have to manually copy them over a new copy. They can't just press a button to action this.
March 8, 2009 at 5:38 pm
When called from the stored procedure in question, I do believe you are all right in saying that the called job tries to run as whoever the user is who created that job, not as the calling Windows Authentication user.
That is not what I said because what you can do is fixed you either give the Agent enough permissions or create a proxy, your current implementation impedes your users. So if you are in a company network give the Agent a domain account with admin level permissions on the network level and SQL Server so your jobs can run. What we are telling you comes without options because these are new requirements by Microsoft.
Kind regards,
Gift Peddie
March 8, 2009 at 5:55 pm
The Windows Authentication Administrator account, which is the one you are referring to, already does have SQLAgent permissions, and runs fine if you are logged onto the server as that user, and execute the SP or the job.
However, when the SP is called from a local user and the job called from within that SP is trying to call that Administrator account to execute the job, it fails. Not because the Administrator account doesn't have the right permissions to execute the job, but because the local user account calling the SP does not have permission to allow the Administrator account to execute the job from the SP.
March 8, 2009 at 6:04 pm
No you don't understand me I am telling you to go into Configuration Manager and use that account to run the Agent because when anything it run as a JOB it is the Agent account that is checked not the person running the JOB.
So in SQL Server 2005 and 2008 go into configuration manager right click on the Agent and change the account used to run it.
Why it is execution context related.
Kind regards,
Gift Peddie
March 8, 2009 at 6:23 pm
Oh, right. I do see what you're saying. You're saying that if I switch the user that executes agent tasks to something that requires a lower permission level, so that my local users can get at it, then my problem is solved.
However, I think it would be rather dangerous to open up all agent tasks to all my users, don't you? That's not really what I want to do.
I would be OK with allowing this one job to be executed by a lower level, so maybe I could use a proxy account to achieve this, but if I got just one permission wrong, I could really mess up my agent access altogether.
Thanks for trying to help but I think I'll leave well enough alone at the moment.
March 8, 2009 at 6:31 pm
No you cannot use a lower level permission because it will not run however that is the reason for the proxy account. The proxy account is not complicated you could try it with your account.
People running the Agent and executing code as JOB are very different, the later the Agent must have relevant permissions.
Kind regards,
Gift Peddie
March 8, 2009 at 6:40 pm
Ok, there is clearly something I'm still not understanding then.
The current agent is set to Administrator. This is the administrator account for the Windows Domain.
When I login as this administrator and run the stored procedure, it executes just fine.
Likewise, if I click on the button in MS Access which runs the stored procedure, when I am logged onto my computer as me (and I have God rights on our system), then it executes fine.
However, when any other user tries the same, it fails.
Therefore, it HAS to be said that the job is failing to execute because the person running the stored procedure does not have the right permissions to either 1) execute the job from the Stored Proc or 2) call the agent that executes the job.
If I use a proxy for this, I don't see how that differs UNLESS the proxy account allows these people with lower permissions to perform all agent tasks.
Can you explain to me how changing to use a proxy would change anything about this process?
March 8, 2009 at 6:55 pm
If I use a proxy for this, I don't see how that differs UNLESS the proxy account allows these people with lower permissions to perform all agent tasks.
The proxy account will allow people with lower permissions to perform Agent tasks but because it is a proxy it cannot be used for anything not defined. But run some tests create the proxy and use the lower level permissions to run the job and see if you can do more that may harm your system. Then implement it based on your tests.
Actually in 2005 the proxy account does just what it was created to be used so just the JOBs in your application and not the whole Agent subsystem. Check the docs below for details.
http://msdn.microsoft.com/en-us/library/ms190698.aspx
Kind regards,
Gift Peddie
March 8, 2009 at 7:03 pm
But if the account being called by the sp is whatever the default SQL Agent account is, then as you point out, I have to change the default SQL Agent account to this proxy account, don't I?
That still sounds sorta dangerous.
I've tried executing the job as a proxy user who has agent access, and that also failed, which I thought was sorta strange.
Anyway, thanks for the help, I'll look at the link you note and see if I can find anything further.
March 8, 2009 at 7:10 pm
The link actually covers it about how it is an admin user account that is used for the proxy not the default Agent account. So create the proxy with your account add the users to it and run some tests.
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply