Changing SQL Server Agent Account

SQL Server Administration
1

We are running SS 2014. When I create a dts package that access data on another server it runs if I use the Execute Package Utility because my Windows logon has rights to access the Excel spreadsheet on the other server. When it runs under SQL Server Agent it fails because (I'm assuming) SQL Server Agent does not have the permissions to access the folder on the other server. My Windows login is part of a group that does have access.

My thought was to create a domain account for SQL Server Agent and add it to the same group that I'm a member of and then change the NT AUTHORITY/SQLSERVEAGENT account that SQL Server Agent runs under to this new domain account.

Is that the proper way to deal with this?

I tried this: I created a SQL Server Credential using my domain account and then used that to create a Proxy under SSIS Package Execution, I then chose that proxy in the Run as box for the step that runs the dts package, but it still fails with the error, "{path to Excel file} is not a valid path". The DTS package reads an Excel file in to a table.

Greg

2

We use the Domain Account for SQL Agent approach just as you suggested. I'd go with that.

3

Thanks for the quick reply. Does it matter if I change at the Service or should I change it from the Configuration Manager?

Greg

4

Just make sure that whatever permission the existing accounts has are also applied to the new account.

5

To know how to change SQL Server Agent Account you should go this article https://support.microsoft.com/en-us/kb/283811
And if you want to reset the login password then there are four methods that can be used to recover the SQL Server lost password. Please go to my this article here you can see all the methods in very short:
How to Recover SA Password in SQL Server Database?

6

I changed the SQL Server Agent account to my own domain account and received the error below. This is the same error I get when i create a proxy with my domain account and use it as the Run As in the step. Again, if I run it using the package execution tool it runs fine.

Any ideas what is going on?

Greg

Executed as user: {Domain\login}. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 12:56:07 PM
Error: 2016-03-22 12:56:08.09
Code: 0xC0202009
Source: {package name} Connection manager "SourceConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005. An OLE DB record is available.
Source: "Microsoft Access Database Engine"
Hresult: 0x80004005
Description: "'{Path to excel file}' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
End Error

7

I wouldn't recommend that option - it isn't necessary. The proper way to handle this would be to create a proxy account (your new domain account), grant access to the share to the proxy account - and then run the SSIS package using the proxy account.

  1. To create the proxy account, first - create a new credential as your new domain account.
  2. Next, create a proxy account with Integration Services access under SQL Server Agent.
  3. Modify your agent job to use the proxy account.
  4. Add the new domain account as a login to SQL Server - and a user in all databases the package needs to access with the appropriate permissions.
  5. Grant the new domain account access to the file share (add to any security groups it needs to be added to also - which may also grant access to SQL Server).
8

In order to be able to access files on another server or file share - you have to use the UNC path name. If you are using a mapped drive - and running the package under your account, then the mapped drive will work because the package is running in the context of your account and can see the mapped drive.

When running from SQL Server Agent - it cannot see your mapped drives (or any mapped drives for that matter) - and it will fail with the above error.

1 Like
9

That's it!!!! UNC file path worked! Thank you, thank you, thank you!

Greg