SQL Server Agent Job Account Issue

SQL Server agent job account issue

I would typically run the SQL Server Agent jobs under the same account as your app accesses the database.

If that account is too limited in its permissions (which might be a good thing!), I would create a single account for that app and all its SQL jobs (if that's possible) and run all SQL jobs under that account.

You could potentially run each step under a different account, but I wouldn't use that in general (it just makes it really hard to know and understand what is run under which account). Only use it if you have to run a particularly sensitive step that needs a bunch of extra permissions and those permissions are only available to a particular system account or something.

The account under which the SQL Server Agent windows service runs really doesn't have an impact on what your job steps will be run under.

So it boils down to really just two accounts:

• one account is needed to run the SQL Server Agent Windows service - this is a Windows account on your machine / server which needs to have enough permissions to run the service, start and stop it - either use LocalSystem, Network Service, or whatever other Windows account you have to run services with

• The other account would be the account to run your SQL Server Agent steps under - that's typically a SQL Server account (which could be based on a Windows account), and it needs enough privileges inside SQL Server to do its job, e.g. it needs access to the database objects and all. I would strive to have just one account for each app that runs the SQL Server jobs - makes life a whole lot easier!

Marc

PS: To set the user to run a step under, you need to use the "Advanced" page on the Job step property dialog and select the user from a popup window:

SQL Server Agent: Can't Start, Won't Start - Changing the 'Log On' account to a Managed Service Account

Unfortunately I had to follow through with my comment above. The only way I could get out of this awful loop was to do a reinstall of SQL. For me this included doing the following:

Backup the databases > uninstall all SQL programs and components > delete all SQL files and folders on all drives > reinstall SQL (ensuring the correct accounts were allocated to the services!) > restore all DB's from backup > check services are running correctly.

It's worth reminding that this fortunately this was in a TEST environment so I had some room to play around. Despite this, this was the only way I found around the problem. I also want to clarify that I came into this task with no prior history in setting up these environments.

SQL Server service and agent account permission changes do I need to restart service to take effect

@Charlieface’s comment explains the reason:

Double check that SQL Server is definitely still running under that service account, as well as which account has now received permissions to the share. The permissions for xp_cmdshell depend on whether the session has sa rights, see learn.microsoft.com/en-us/sql/relational-databases/…. Quite why you are using xp_cmdshell is a different question: it has major security implications, and should probably be converted into a SQL Agent job written in Powershell.

SQL Agent job failure with SSIS package to Access DB

It turns out that the problem was that the Jet provider was trying to write to the SQL Agent user's temp directory, even though the task was being run with impersonation as a different user. This appears to be a feature of the Windows impersonation system, which does not change the user profile, only the user token. I ended up with this code:

var tempPath = Path.GetTempPath().Replace("\\SQLSERVERAGENT\\", "\\" + Environment.UserName + "\\");
Environment.SetEnvironmentVariable("TEMP", tempPath);
Environment.SetEnvironmentVariable("TMP", tempPath);

It's not ideal, but it works. It means that I don't have to give permissions to the SQL Agent's temp directory. Only this code has to change.

Sadly, there appears to be no way to change where the ODBC driver puts its temporary files.

EDIT: I also had this issue with a regular data flow-based package with an Excel source. In that case, I had no choice but to grant access to the SQL Agent's temp directory for my proxy user's account. If I can come up with a workaround there too, I will post it.

Service account deleted from SQL Server but SQL Agent Job is still running under the context of the deleted account. How?

To view logins you'll need to have VIEW DEFINITION granted to your login for each login account that you want to view. If you want to view all logins, then ALTER ANY LOGIN will need to be granted. I'm guessing you wouldn't grant these to yourself, but to get an idea of them an example of these commands is below. These commands must been run from the master database as noted.

VIEW DEFINITION:

USE MASTER;

GRANT VIEW DEFINITION ON LOGIN::LoginToView TO YourLogin

ALTER ANY LOGIN:

USE MASTER;

GRANT ALTER ANY LOGIN to YourLogin

What is the meaning of SQL server agent service account in SQL-job?

Run as defines the proxy account to be used to run this step. Proxy accounts defines a security context in which this job step runs. Each proxy corresponds to a security credential. For example, if you try to execute a copy command with CmdExec type, you must use a credential (e.g. Windows user account) that has rights to read the source file and rights to write in the destination folder.

Job steps can be different types:

• Executable programs and operating system commands.

• Transact-SQL statements, including stored procedures and extended stored procedures.

• PowerShell scripts.

• Microsoft ActiveX scripts.

• Replication tasks.

• Analysis Services tasks.

• Integration Services packages.

Each type is executed differently. T-SQL scripts are sent to the database engine, executable programs (CmdExec) starts external programs (e.g. copy to copy files, or DTSRun to run a DTS package outside of SQL Server, as in your example), etc.

---

Related Topics