%3CLINGO-SUB%20id%3D%22lingo-sub-1243033%22%20slang%3D%22en-US%22%3ESecure%20AND%20Easy%20Service%20Account%20Management%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1243033%22%20slang%3D%22en-US%22%3E%3CP%3ELike%20most%20companies%2C%20we%20have%20various%20automations%20and%20tools%20we%20use%20to%20do%20everything%20it%20takes%20to%20run%20Microsoft%20Endpoint%20Manager%20Configuration%20Manager%20(a.k.a.%20MEMCM%2C%20MECM%2C%20SCCM%2C%20System%20Center%20Configuration%20Manager%2C%20or%20even%20SMS%20but%20we%E2%80%99ll%20refer%20to%20it%20as%20MECM)%20and%20Intune.%20To%20have%20those%20run%20well%20we%20follow%20the%20common%20best%20practice%20of%20using%20dedicated%20service%20accounts.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20example%20of%20that%20is%20for%20running%20the%20SQL%20services%2C%20which%20is%20the%20back%20end%20for%20MECM.%20For%20many%20years%2C%20the%20best%20practice%20was%20to%20have%20a%20service%20account%20for%20each%20SQL%20service%20%E2%80%93%20and%20technically%20for%20each%20instance%20of%20SQL.%20We%20used%20separate%20accounts%20but%20not%20for%20each%20server%20because%20the%20management%20was%20painful%20enough%20as%20it%20was.%20We%20had%20to%20perform%20password%20rotations%20on%20each%20server%2Fservice%20regularly%20%E2%80%93%20and%20store%20those%20passwords%20securely%20somewhere.%20While%20I%E2%80%99d%20like%20to%20think%20that%20we%20did%20a%20great%20job%20of%20keeping%20all%20this%20secure%2C%20the%20truth%20is%20the%20passwords%20had%20to%20be%20copied%20somewhere%20for%20people%20to%20perform%20the%20password%20rotation.%20And%20I%E2%80%99m%20sure%20at%20times%20passwords%20were%20shared%20in%20unsecure%20ways.%20So%2C%20to%20improve%20our%20security%20and%20make%20our%20lives%20easier%20at%20the%20same%20time%2C%20we%20decided%20to%20switch%20to%20using%20a%20Group%20Managed%20Service%20Account%20(gMSA).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20remarkable%20thing%20about%20a%20gMSA%20is%20that%20Active%20Directory%20(AD)%20manages%20the%20password%20for%20you.%20That%E2%80%99s%20right%2C%20you%20don%E2%80%99t%20have%20to%20create%2C%20store%2C%20or%20update%20the%20password!%20So%20there%E2%80%99s%20no%20chance%20that%20it%E2%80%99ll%20get%20compromised.%20Additionally%2C%20the%20SQL%20services%20don%E2%80%99t%20require%20a%20restart%20when%20the%20password%20is%20updated%2C%20which%20is%20required%20when%20the%20password%20changes%20for%20a%20service%20account.%20Pretty%20great%20right%3F%20You%20can%20do%20your%20own%20reading%20and%20research%20on%20gMSA%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fwindows-server%2Fsecurity%2Fgroup-managed-service-accounts%2Fgroup-managed-service-accounts-overview%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fwindows-server%2Fsecurity%2Fgroup-managed-service-accounts%2Fgroup-managed-service-accounts-overview%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBefore%20I%20continue%2C%20I%E2%80%99d%20like%20to%20mention%20some%20of%20my%20friends%20who%20work%20at%20Tata%20Consultancy%20Services%20who%20did%20most%20of%20the%20work%20to%20make%20all%20this%20happen%3A%20Rakesh%20Silam%2C%20RamPratap%20Yadav%2C%20Varun%20Bejugam%2C%20and%20Dhanraj%20Rajendraodayar.%20These%20are%20some%20of%20the%20best%20admins%20you%20could%20ever%20work%20with.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlright%2C%20let%E2%80%99s%20get%20into%20the%20implementation%20info...here%20is%20what%20we%20did%20to%20get%20our%20SQL%20Server%20services%20running%20with%20a%20gMSA.%20Note%3A%20this%20is%20not%20meant%20to%20be%20a%20step%20by%20step%20guide.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId--682989102%22%20id%3D%22toc-hId--682988976%22%20id%3D%22toc-hId--682988976%22%20id%3D%22toc-hId--682988976%22%20id%3D%22toc-hId--682988976%22%20id%3D%22toc-hId--682988976%22%20id%3D%22toc-hId--682988976%22%3EPre-Requisites%3C%2FH4%3E%0A%3CP%3EFirst%20make%20sure%20to%20look%20through%20the%20documentation%20mentioned%20above%20for%20the%20pre-reqs%20to%20ensure%20you%20meet%20all%20of%20them.%20For%20AD%20to%20generate%20passwords%20for%20gMSAs%2C%20a%20KDS%20root%20key%20needs%20to%20be%20created.%20You%20can%20follow%20the%20information%20at%20this%20location%20if%20you%20need%20to%20do%20that%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Fwindows%2Fit-pro%2Fwindows-server-2012-R2-and-2012%2Fjj128430%2528v%253dws.11%2529%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Fwindows%2Fit-pro%2Fwindows-server-2012-R2-and-2012%2Fjj128430%2528v%253dws.11%2529%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-1804523731%22%20id%3D%22toc-hId-1804523857%22%20id%3D%22toc-hId-1804523857%22%20id%3D%22toc-hId-1804523857%22%20id%3D%22toc-hId-1804523857%22%20id%3D%22toc-hId-1804523857%22%20id%3D%22toc-hId-1804523857%22%3EImplementation%3C%2FH4%3E%0A%3CP%3EFor%20simplicity%20sake%20we%20wanted%20to%20be%20able%20to%20use%20one%20gMSA%20for%20all%20our%20SQL%20Servers.%20This%20may%20help%20explain%20why%20we%20went%20with%20the%20following%20steps%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ECreate%20a%20security%20group%20for%20the%20servers%20on%20which%20the%20gMSA%20will%20run%3C%2FLI%3E%0A%3CLI%3EAdd%20the%20servers%20on%20which%20the%20gMSA%20will%20run%20into%20the%20security%20group%3C%2FLI%3E%0A%3CLI%3ECreate%20a%20gMSA%20account%3COL%20class%3D%22lia-list-style-type-lower-alpha%22%3E%0A%3CLI%3EThis%20needs%20to%20be%20done%20via%20PowerShell%2C%20the%20command%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Factivedirectory%2Fnew-adserviceaccount%3Fview%3Dwinserver2012-ps%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ENew-ADServiceAccount%3C%2FA%3E%20is%20what%20you%20use.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FLI%3E%0A%3CLI%3ECreate%20Service%20Principal%20Names%20(SPNs)%20for%20the%20SQL%20Service%20and%20gMSA%3COL%20class%3D%22lia-list-style-type-lower-alpha%22%3E%0A%3CLI%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Fregister-a-service-principal-name-for-kerberos-connections%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Fregister-a-service-principal-name-for-kerberos-connections%3Fview%3Dsql-server-ver15%3C%2FA%3E%20for%20an%20overview%20of%20SPNs.%3C%2FLI%3E%0A%3CLI%3EAs%20a%20sanity%20check%2C%20you%20can%20run%20%E2%80%9Csetspn%20-l%20%5Bnameofyourgmsa%5D%E2%80%9D%20to%20ensure%20that%20the%20SPNs%20exist%20and%20to%20ensure%20that%20they%20are%20set%20correctly.%20You%20should%20see%20the%20entries%20in%20the%20following%20format%3A%26nbsp%3B%20%22MSSQLSvc%2F%5BServer%20Name%20-%20with%2Fwithout%20FQDN%5D%3A%5Bport%20number%5D%22%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FLI%3E%0A%3CLI%3EOptional%3A%20Create%20constrained%20delegation%3COL%20class%3D%22lia-list-style-type-lower-alpha%22%3E%0A%3CLI%3EWe%20make%20use%20of%20linked%20server%20queries%20from%20time%20to%20time%20so%20in%20order%20to%20avoid%20a%20double%20hop%20issue%20we%20enable%20constrained%20delegation%20for%20the%20SQL%20Servers.%20In%20fact%2C%20we%20use%20bi-directional%20constrained%20delegation%20to%20avoid%20issues%20that%20could%20arise%20based%20on%20from%20which%20server%20the%20query%20is%20started.%3C%2FLI%3E%0A%3CLI%3EFor%20more%20information%20about%20constrained%20delegation%20see%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fwindows-server%2Fsecurity%2Fkerberos%2Fkerberos-constrained-delegation-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fwindows-server%2Fsecurity%2Fkerberos%2Fkerberos-constrained-delegation-overview%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FLI%3E%0A%3CLI%3EUpdate%20the%20service%20account%20in%20SQL%20Server%20Configuration%20Manager%20to%20use%20the%20gMSA%3COL%20class%3D%22lia-list-style-type-lower-alpha%22%3E%0A%3CLI%3EWhen%20you%20add%20the%20gMSA%20you%20do%20not%20need%20to%20fill%20the%20password%20in%2C%20just%20add%20the%20account%20and%20apply.%20AD%20takes%20care%20of%20the%20password%20for%20you!%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CH4%20id%3D%22toc-hId--2930732%22%20id%3D%22toc-hId--2930606%22%20id%3D%22toc-hId--2930606%22%20id%3D%22toc-hId--2930606%22%20id%3D%22toc-hId--2930606%22%20id%3D%22toc-hId--2930606%22%20id%3D%22toc-hId--2930606%22%3EConclusion%3C%2FH4%3E%0A%3CP%3EWith%20all%20that%20completed%20all%20our%20SQL%20Server%20services%20are%20running%20under%20the%20gMSA.%20We%20no%20longer%20worry%20about%20password%20management%2Frotation%20and%20we%20have%20increased%20security.%20Will%20you%20be%20moving%20your%20services%20to%20run%20under%20a%20gMSA%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424484%22%20slang%3D%22en-US%22%3ERe%3A%20Secure%20AND%20Easy%20Service%20Account%20Management%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295121%22%20target%3D%22_blank%22%3E%40SqlBenjamin%3C%2FA%3E%26nbsp%3BOnce%20you%20create%20the%20gmsa%20do%20you%20need%20to%20add%20the%20gmsa%20to%20the%20SQL%20database%20folder%20security%20permissions%20with%20full%20control%3F%26nbsp%3B%20%26nbsp%3BI%20am%20getting%20the%20following%20error%20in%20the%20Config%20Mgr%20setup%20log%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EINFO%3A%20SQL%20Connection%20succeeded.%20Connection%3A%20SMS%20ACCESS%2C%20Type%3A%20Secure%20%24%24%3CCONFIGURATION%20manager%3D%22%22%20setup%3D%22%22%3E%26lt%3B05-27-2020%2013%3A02%3A04.700%2B360%26gt%3B%3CTHREAD%3E%3CSCX-%3E%3CBR%20%2F%3E1%2F%26gt%3BCreate_BackupSQLCert%20%3A%20SQL%20server%20failed%20to%20backup%20cert.%20%24%24%3CCONFIGURATION%20manager%3D%22%22%20setup%3D%22%22%3E%26lt%3B05-27-2020%2013%3A02%3A14.888%2B360%26gt%3B%3CTHREAD%3E%3CSC%3E%3CBR%20%2F%3Ex-1%2F%26gt%3BCSiteControlSetup%3A%3ASetupCertificateForSSB%20%3A%20Failed%20to%20create%2Fbackup%20SQL%20SSB%20certificate.%20%24%24%3CCONFIGURATION%20manager%3D%22%22%20setup%3D%22%22%3E%26lt%3B05-27-2020%2013%3A02%3A14.888%2B360%26gt%3B%3CTHREAD%3E%3CSC%3E%3CBR%20%2F%3Ex-1%2F%26gt%3BERROR%3A%20Failed%20to%20set%20up%20SQL%20Server%20certificate%20for%20service%20broker%20on%20%22SQL.server.fqdn%22%20.%20%24%24%3CCONFIGURATION%20manager%3D%22%22%20setup%3D%22%22%3E%26lt%3B05-27-2020%2013%3A02%3A14.888%2B360%26gt%3B%3CTHREAD%3E%3CSCX%3E%3CBR%20%2F%3E-1%2F%26gt%3BERROR%3A%20Failed%20to%20initialize%20the%20site%20control%20data.%20%24%24%3CCONFIGURATION%20manager%3D%22%22%20setup%3D%22%22%3E%26lt%3B05-27-2020%2013%3A02%3A14.888%2B360%26gt%3B%3CTHREAD%3E%3C%2FTHREAD%3E%3C%2FCONFIGURATION%3E%3C%2FSCX%3E%3C%2FTHREAD%3E%3C%2FCONFIGURATION%3E%3C%2FSC%3E%3C%2FTHREAD%3E%3C%2FCONFIGURATION%3E%3C%2FSC%3E%3C%2FTHREAD%3E%3C%2FCONFIGURATION%3E%3C%2FSCX-%3E%3C%2FTHREAD%3E%3C%2FCONFIGURATION%3E%3C%2FP%3E%3CP%3EI%20switched%20out%20the%20%22SQL.server.fqdn%22%20for%20security%20purposes.%26nbsp%3B%20It%20is%20the%20fqdn%20of%20our%20sql%20server%20which%20is%20separate%20fro%20the%20SCCM%20server.%3C%2FP%3E%3CP%3EAppreciate%20the%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424545%22%20slang%3D%22en-US%22%3ERe%3A%20Secure%20AND%20Easy%20Service%20Account%20Management%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F682569%22%20target%3D%22_blank%22%3E%40nbuck2480%3C%2FA%3E%26nbsp%3B%20What%20permissions%20does%20the%20gMSA%20have%20on%20the%20boxes%3F%20If%20they%20are%20admins%20then%20no%20perms%20should%20be%20required%2C%20if%20not%20then%20you%20will%20need%20to%20make%20sure%20the%20ACLs%20are%20setup%20for%20the%20account%20in%20the%20appropriate%20folders.%20What%20perms%20in%20SQL%20does%20the%20gMSA%20have%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424694%22%20slang%3D%22en-US%22%3ERe%3A%20Secure%20AND%20Easy%20Service%20Account%20Management%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424694%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20added%20the%20DBE%20gmsa%20to%20the%20administrators%20in%20my%20database%20server%20GPO%2C%20I%20am%20going%20to%20test%20it.%20Would%20giving%20full%20control%20to%20the%20Database%20and%20log%20file%20structures%20(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Fconfigure-file-system-permissions-for-database-engine-access%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Fconfigure-file-system-permissions-for-database-engine-access%3Fview%3Dsql-server-ver15%3C%2FA%3E)%20be%20enough%20permissions%20if%20I%20am%20not%20giving%20them%20Administrators%20group%20membership%3F%26nbsp%3B%20I%20ask%20for%20the%20sake%20of%20least%20privilege.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20before%20I%20made%20the%20changes%20an%20hour%20ago%20the%20GMSA%20was%20created%20and%20given%20a%20spn%20as%20you%20have%20instructed%20in%20this%20blog.%26nbsp%3B%20I%20assigned%20the%20gmsas%20via%20the%20SQL%20setup.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425161%22%20slang%3D%22en-US%22%3ERe%3A%20Secure%20AND%20Easy%20Service%20Account%20Management%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425161%22%20slang%3D%22en-US%22%3E%3CP%3EAlright%20it%20seems%20that%20adding%20the%20gmsa%20to%20the%20administrators%20group%20fixed%20the%20%22%3CSPAN%3EFailed%20to%20create%2Fbackup%20SQL%20SSB%20certificate%22%20issue.%26nbsp%3B%20And%20the%20sccm%20console%20is%20now%20working!%26nbsp%3B%20But%20I%20noted%20that%20the%20log%20still%20shows%20the%20following%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EERROR%3A%20SQL%20Connection%20failed.%20Connection%3A%20CCAR_DB_ACCESS%2C%20Type%3A%20Secure%20%24%24%3CCONFIGURATION%20manager%3D%22%22%20setup%3D%22%22%3E%26lt%3B05-27-2020%2013%3A01%3A54.294%2B360%26gt%3B%3CTHREAD%3E%3CSCX%3E%3CBR%20%2F%3E-1%2F%26gt%3BFailed%20to%20get%20DB%20connection%20for%20turning%20off%20client%20piloting%20for%20CD%20upgrade.%20%24%24%3CCONFIGURATION%20manager%3D%22%22%20setup%3D%22%22%3E%26lt%3B05-27-2020%2013%3A01%3A54.294%2B360%26gt%3B%3CTHREAD%3E%3C%2FTHREAD%3E%3C%2FCONFIGURATION%3E%3C%2FSCX%3E%3C%2FTHREAD%3E%3C%2FCONFIGURATION%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENot%20sure%20if%20you%20could%20help%20me%20with%20that.%26nbsp%3B%20I%20appreciate%20your%20help!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425224%22%20slang%3D%22en-US%22%3ERe%3A%20Secure%20AND%20Easy%20Service%20Account%20Management%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F682569%22%20target%3D%22_blank%22%3E%40nbuck2480%3C%2FA%3E%26nbsp%3B%20are%20there%20any%20INFO%20messages%20before%20the%20connection%20failed%20message%3F%20Did%20you%20restart%20the%20services%20after%20updating%20the%20account%20and%20perms%3F%20Depending%20on%20the%20steps%20things%20were%20done%20in%20that%20could%20be%20an%20issue%20but%20not%20very%20likely%20in%20this%20case...but%20worth%20asking%2Fthrowing%20out%20there.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

Like most companies, we have various automations and tools we use to do everything it takes to run Microsoft Endpoint Manager Configuration Manager (a.k.a. MEMCM, MECM, SCCM, System Center Configuration Manager, or even SMS but we’ll refer to it as MECM) and Intune. To have those run well we follow the common best practice of using dedicated service accounts.

 

One example of that is for running the SQL services, which is the back end for MECM. For many years, the best practice was to have a service account for each SQL service – and technically for each instance of SQL. We used separate accounts but not for each server because the management was painful enough as it was. We had to perform password rotations on each server/service regularly – and store those passwords securely somewhere. While I’d like to think that we did a great job of keeping all this secure, the truth is the passwords had to be copied somewhere for people to perform the password rotation. And I’m sure at times passwords were shared in unsecure ways. So, to improve our security and make our lives easier at the same time, we decided to switch to using a Group Managed Service Account (gMSA).

 

The remarkable thing about a gMSA is that Active Directory (AD) manages the password for you. That’s right, you don’t have to create, store, or update the password! So there’s no chance that it’ll get compromised. Additionally, the SQL services don’t require a restart when the password is updated, which is required when the password changes for a service account. Pretty great right? You can do your own reading and research on gMSA here: https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/group-manage...

 

Before I continue, I’d like to mention some of my friends who work at Tata Consultancy Services who did most of the work to make all this happen: Rakesh Silam, RamPratap Yadav, Varun Bejugam, and Dhanraj Rajendraodayar. These are some of the best admins you could ever work with.

 

Alright, let’s get into the implementation info...here is what we did to get our SQL Server services running with a gMSA. Note: this is not meant to be a step by step guide.

 

Pre-Requisites

First make sure to look through the documentation mentioned above for the pre-reqs to ensure you meet all of them. For AD to generate passwords for gMSAs, a KDS root key needs to be created. You can follow the information at this location if you need to do that: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/jj...

 

Implementation

For simplicity sake we wanted to be able to use one gMSA for all our SQL Servers. This may help explain why we went with the following steps

  1. Create a security group for the servers on which the gMSA will run
  2. Add the servers on which the gMSA will run into the security group
  3. Create a gMSA account
    1. This needs to be done via PowerShell, the command New-ADServiceAccount is what you use.
  4. Create Service Principal Names (SPNs) for the SQL Service and gMSA
    1. See https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-... for an overview of SPNs.
    2. As a sanity check, you can run “setspn -l [nameofyourgmsa]” to ensure that the SPNs exist and to ensure that they are set correctly. You should see the entries in the following format:  "MSSQLSvc/[Server Name - with/without FQDN]:[port number]"
  5. Optional: Create constrained delegation
    1. We make use of linked server queries from time to time so in order to avoid a double hop issue we enable constrained delegation for the SQL Servers. In fact, we use bi-directional constrained delegation to avoid issues that could arise based on from which server the query is started.
    2. For more information about constrained delegation see: https://docs.microsoft.com/en-us/windows-server/security/kerberos/kerberos-constrained-delegation-ov...
  6. Update the service account in SQL Server Configuration Manager to use the gMSA
    1. When you add the gMSA you do not need to fill the password in, just add the account and apply. AD takes care of the password for you!

Conclusion

With all that completed all our SQL Server services are running under the gMSA. We no longer worry about password management/rotation and we have increased security. Will you be moving your services to run under a gMSA?

5 Comments
Visitor

@SqlBenjamin Once you create the gmsa do you need to add the gmsa to the SQL database folder security permissions with full control?   I am getting the following error in the Config Mgr setup log:

 

INFO: SQL Connection succeeded. Connection: SMS ACCESS, Type: Secure $$<Configuration Manager Setup><05-27-2020 13:02:04.700+360><thread=980 (0x3D4)>
Create_BackupSQLCert : SQL server failed to backup cert. $$<Configuration Manager Setup><05-27-2020 13:02:14.888+360><thread=980 (0x3D4)>
CSiteControlSetup::SetupCertificateForSSB : Failed to create/backup SQL SSB certificate. $$<Configuration Manager Setup><05-27-2020 13:02:14.888+360><thread=980 (0x3D4)>
ERROR: Failed to set up SQL Server certificate for service broker on "SQL.server.fqdn" . $$<Configuration Manager Setup><05-27-2020 13:02:14.888+360><thread=980 (0x3D4)>
ERROR: Failed to initialize the site control data. $$<Configuration Manager Setup><05-27-2020 13:02:14.888+360><thread=980 (0x3D4)>

I switched out the "SQL.server.fqdn" for security purposes.  It is the fqdn of our sql server which is separate fro the SCCM server.

Appreciate the help!

 

Microsoft

@nbuck2480  What permissions does the gMSA have on the boxes? If they are admins then no perms should be required, if not then you will need to make sure the ACLs are setup for the account in the appropriate folders. What perms in SQL does the gMSA have?

Visitor

I just added the DBE gmsa to the administrators in my database server GPO, I am going to test it. Would giving full control to the Database and log file structures (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-file-system-permiss...) be enough permissions if I am not giving them Administrators group membership?  I ask for the sake of least privilege.

 

But before I made the changes an hour ago the GMSA was created and given a spn as you have instructed in this blog.  I assigned the gmsas via the SQL setup.

Visitor

Alright it seems that adding the gmsa to the administrators group fixed the "Failed to create/backup SQL SSB certificate" issue.  And the sccm console is now working!  But I noted that the log still shows the following:

 

ERROR: SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Secure $$<Configuration Manager Setup><05-27-2020 13:01:54.294+360><thread=980 (0x3D4)>
Failed to get DB connection for turning off client piloting for CD upgrade. $$<Configuration Manager Setup><05-27-2020 13:01:54.294+360><thread=980 (0x3D4)>

 

Not sure if you could help me with that.  I appreciate your help!

Microsoft

@nbuck2480  are there any INFO messages before the connection failed message? Did you restart the services after updating the account and perms? Depending on the steps things were done in that could be an issue but not very likely in this case...but worth asking/throwing out there.