SQL Server PowerShell provider and Service Accounts RRS feed

  • General discussion

  • How do you guys handle Windows accounts used for automating jobs a DBA would do?  In the past I've had my active directory admins create an account to be used by DBAs for automation purposes.  It would be named so that it was apparent what its purpose was.  For example svcDBAJobAutomation.  It would then be added to the DBA group in AD which in turn was a member of sysadmin group in SQL.  None of our servers allowed mixed mode authentication.  Then when I wanted to create a job to run automatically that acted like a DBA I would use that account.  For example, a job used to read some job logs on each SQL server in a list and email me a report if any errors were detected.

    I ask because I'm doing some scripting with PowerShell 2.0, WMI, and ADO.NET to audit my database servers at my current employers.  The job will run on one central management SQL server and collect information from all the other ones.  In test mode it works perfectly under my security context.  I find myself in a situation where the other DBA does not want to use an AD account but have everything run as the same sa account on all the servers. 

    I have issue with this for a couple of reasons.  First, when I look in BOL under Using the SQL Server PowerShell Provider -> Managing SQL Server Authentication Connections I see no way not to do this without getting prompted for a username and password.  That pretty much shoots myself in the foot regarding automating this.  Second, even if I figure out how to do this without prompting it means hard coding the sa password in a script.  Third, I still need an AD account for the process to run under to make the calls to the various WMI providers I get OS related info from.

    I think that having the AD team create me an account for automation use is the best way to go.  What do you guys think?  How do you handle operational service accounts used for automating your work?  Do any of you hard code sa?

    Joe Moyle
    Tuesday, December 8, 2009 9:58 PM

All replies

  • Using AD accounts is probably your only option if you have to talk WMI.
    Some dbas are set in their ways, so if you have to compromise a part of your script and use SQL logins, have the dbas store the credentials in an encrypted column in a table on the central server.
    All you then have to do is modify your script to extract decrypt the pwd from that table. There's a number of procs out there that do this which you could use as a template.

    ajmer dhariwal || eraofdata.com
    Wednesday, December 9, 2009 5:01 PM
  • Thank you for your input Ajmer.  I hadn't thought of encrypting the sa password in a table on the central machine.  I'll bring that up to my peer.
    Joe Moyle
    Wednesday, December 9, 2009 11:24 PM