2

I want to use a Managed ADServiceAccount created using the following powershell command as a proxy account to schedule and execute ssis packages :

New-ADServiceAccount -Name seraccname -Enable $true -DNSHostName domainname.local
Set-ADServiceAccount -Identity seraccname -PrincipalsAllowedToRetrieveManagedPassword  serv1.domainname.local
Install-ADServiceAccount seraccname

But when i use that account as a crediantion in SQL Server. SSMS expects a password and gives me this error.

"The secret stored in the password field is blank".

However these type account dont have a password as they are managed by AD. Then i tought i was smart and i used the following sql code to force create the credientials without a password

USE [master] 
GO
CREATE CREDENTIAL [seracccred] WITH IDENTITY = 'domainname.local\seraccname$'
GO

I then created a proxy with that credential, used it in a job step (run as) But when i execute the job step i get the following error.

Error authenticating proxy domainname.local\seraccname$, system error, username or password is incorrect

I gave the service account the required permissions to work with SSISDB. but i think thats not the issue. i dont know why SQL Server Agent or SQL server or whatever is expecting a password. I am using SQL Server 2012

Finally, i want to mention that i am reproducing a problem so i dont want an alternative solution . I just want to use an active directory managed service account to execute the job steps and ssis packages.

I think i really misunderstand something here and there is no documentation about this at all which is weird as i see it as a very valid usecase to use managed AD service account to be used as the "run as" of the ssis jobs

Noobie
  • 21
  • 3

1 Answers1

0

If a password wasn't necessary, then anyone could implement the execution of code in the context of an account with higher privileges.

In order to allow SQL Server Agent to execute code in the context of a Proxy Account, it has to know the password to be able to initiate a security session for that account. It would be horrible, if no password was needed for that. You see that Managed Accounts are not useful in this scenario.

But why not run SQL Server Agent under such a Managed Service Account?

Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
  • 1
    Thanks for your comment, but shouldnt the PrincipalsAllowedToRetrieveManagedPassword option in the Set-ADServiceAccount, defines which machines or domain groups are able to use that account without the password as it is managed by AD – Noobie Jul 29 '18 at 01:44
  • The Proxy Account credentials are not used to start a **service**, but you are trying to use an account type that was especially designed for **serives**. – Wolfgang Kais Jul 29 '18 at 08:45