Wednesday, May 10, 2017

Error authenticating proxy username or password is incorrect

We know that PackageExecuter is associated with a SQL Credential that was a former domain admin account. SQL Server saves that Account and Password and then applies it where appropriate. The domain account password used to create the package is different from the service account password used to run the package in QA. 
This is very common error in SQL Server and mostly occurs whenever you want to run your SQL job by domain user instead of SQL Server Agent.
If you want to run the SQL jobs with domain user account then you should be ensured the following points-
  1. Configure a User to Create and Manage SQL Server Agent Jobs
  2. Reset the SQL Server Agent Services
How to configure a user account in SQL Server?
Before creating or configure domain user account, you should have access rights to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts. You should have fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.
If non-administrative users want to run jobs that execute other job step types (for example, SSIS packages), they will need to have access to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts.
To configure a user to create and manage SQL Server Agent jobs, we should follow the following steps in SQL Server Management Studio-
Just go to security folder and check login you wish to add to a SQL Server Agent fixed database role, and select Properties.
In login properties window, go to User Mapping page and select the row containing msdb. After that check the appropriate SQL Server Agent fixed database role under Database role membership for: msdb

Just click OK Button to close login properties window.
How to configure a user Credentials in SQL Server?
After setting the membership, to run the SQL Server Agent jobs for a domain user we have to create the credentials as given below-
In Credential Window, we have to set the credential name and provide the identity of the login user with password as given below-
Click on OK button to close credential window.
How to configure a Proxy in SQL Server?
Now, we have to go SQL Server Agent to create a proxy under SSIS Package Execution as given below-
In Proxy Account window, we have to fill Proxy name and assign credential name which we have created in credential under security folder of SQL Server. After assigning credentials, we have to active all subsystems as given below-
Reset the SQL Server Agent Services
After creating the proxy account, click OK button to close proxy account window. Now, it is time to reset the SQL Server Agent Services. Just go to the window services, right click on SQL Server Agent Services and click on properties as given below-

In SQL Server Agent properties window, just go to Log On tab and set the domain user to run the SQL Server Agent services as given below-
After clicking on Apply button, click on OK button to close SQL Server Agent properties window. Now, we have to restart SQL Server Agent window services for again as given below-

Now we have done everything around this issue and we are able to run our all packages in SQL Server with the domain user account. We should take care that if domain user password will change then we need to change the new password in SQL Server Agent also.

Popular Posts