%3CLINGO-SUB%20id%3D%22lingo-sub-860787%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860787%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20to%20login%20to%20the%20test%20database%20using%20the%20SSMS%20and%20not%20through%20code%3F%20Is%20this%20even%20supported%3F%20If%20yes%2C%20What%20would%20be%20the%20option%20to%20choose%20for%20authentication%20to%20sign%20in%20and%20is%20the%20secret%20key%20the%20password%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1076116%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1076116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410944%22%20target%3D%22_blank%22%3E%40jnprakash%3C%2FA%3E%26nbsp%3BYou%20can%20not%20login%20to%20the%20Azure%20SQL%20with%20SSMS%20using%20the%20service%20principal%20as%20I%20know%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdba.stackexchange.com%2Fquestions%2F184598%2Funable-to-connect-using-azure-ad-service-principal-on-sql-server%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdba.stackexchange.com%2Fquestions%2F184598%2Funable-to-connect-using-azure-ad-service-principal-on-sql-server%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356666%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356666%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F225609%22%20target%3D%22_blank%22%3E%40Mirek%20Sztajno%3C%2FA%3E%26nbsp%3BVery%20good%20tutorial%2C%20but%20is%20there%20a%20way%20to%20run%203%20step%20automatically%20in%20CI%2FCD%3F%20with%20Powershell%20or%20Azure%20Devops%20or%20somehow%20else%20but%20automatically%20%3D%20without%20SSMS%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1421877%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F410944%22%20target%3D%22_blank%22%3E%40jnprakash%3C%2FA%3E%26nbsp%3BAt%20this%20point%20we%20do%20not%20have%20this%20option%20in%20our%20client%20tools%20like%20SSMS.%20I%20will%20update%20once%20we%20have%20added%20this%20support.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1421910%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1421910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646152%22%20target%3D%22_blank%22%3E%40R-Chaser%3C%2FA%3E%26nbsp%3B%2C%20there%20are%20some%20possibilities%2C%20depending%20on%20your%20scenario.%20Can%20you%20please%20email%20us%20at%26nbsp%3B%20%3CA%20href%3D%22mailto%3ASQLAADAuth%40microsoft.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESQLAADAuth%40microsoft.com%20%3C%2FA%3Eand%20we%20can%20discuss%20the%20options%20with%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1498271%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F680552%22%20target%3D%22_blank%22%3E%40AmolAgarwalSQL%3C%2FA%3E%26nbsp%3BI%20have%20the%20same%20issue%20as%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646152%22%20target%3D%22_blank%22%3E%40R-Chaser%3C%2FA%3E%26nbsp%3Band%20already%20wrote%20to%20the%20mailing%20address%20you%20mentioned%20but%20I%20haven't%20got%20any%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20share%20some%20feedback%20here%20how%20to%20automate%20this%20process%20in%20Azure%20Pipelines%20when%20it%20is%20NOT%20possible%20to%20create%20external%20(AD)%20SQL%20users%20when%20signed%20in%20as%20Service%20Principal%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1499083%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1499083%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20contact%26nbsp%3B%3CA%20href%3D%22mailto%3ASQLAADFeedback%40microsoft.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESQLAADFeedback%40microsoft.com%3C%2FA%3E%26nbsp%3Balias%20to%20discuss%20the%20details%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1499096%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1499096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F225609%22%20target%3D%22_blank%22%3E%40Mirek%20Sztajno%3C%2FA%3E%26nbsp%3B%20can%20you%20please%20reach%20out%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646152%22%20target%3D%22_blank%22%3E%40R-Chaser%3C%2FA%3E%26nbsp%3Band%20discuss%20options%20with%20him.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1649847%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1649847%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20is%20the%20approach%20for%20handling%20expiration%20of%20token%3F%20We%20have%20a%20spring%20boot%20app%20in%20our%20AKS%20and%20it's%20restarting%20every%20hour%20because%20the%20AAD%20token%20to%20connect%20to%20AZURE%20SQL%20expires%20every%20hour.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1651110%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1651110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F787487%22%20target%3D%22_blank%22%3E%40MarcusDNguyen%3C%2FA%3E%26nbsp%3B%2C%20if%20token%20is%20expired%2C%20client%20has%20to%20get%20a%20new%20token%20from%20AAD%20and%20connect.%20So%2C%20a%20retry%20logic%20is%20needed%20which%20can%20detect%20that%20token%20has%20expired%20and%20gets%20a%20new%20token.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBelow%20is%20the%20link%20from%20AAD%20on%20token%20lifetime.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory-b2c%2Ftokens-overview%23%3A~%3Atext%3DAccess%2520%2526%2520ID%2520token%2520lifetimes%2520%2528minutes%2529%2520-%2520The%2C5%2520minutes.%2520The%2520maximum%2520%2528inclusive%2529%2520is%25201440%2520minutes.%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory-b2c%2Ftokens-overview%23%3A~%3Atext%3DAccess%2520%2526%2520ID%2520token%2520lifetimes%2520%2528minutes%2529%2520-%2520The%2C5%2520minutes.%2520The%2520maximum%2520%2528inclusive%2529%2520is%25201440%2520minutes.%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1599933%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599933%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20we%20deployed%20the%20production%2C%20there%20is%20no%20client%20secret%20key%20anymore.%26nbsp%3B%20How%20will%20the%20example%20work%20without%20a%20client%20secret%20key%3F%26nbsp%3B%20Do%20we%20just%20leave%20that%20blank%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CAMOL%3E%20This%20will%20not%20work%20without%20a%20secret%20key%2C%20how%20is%20your%20app%20getting%20a%20token%2C%20Once%20you%20have%20a%20token%2C%20you%20can%20directly%20put%20it%20here%26nbsp%3B%20and%20then%20connect.%20Token%20will%20start%20with%20something%20like%26nbsp%3Bey....%3C%2FAMOL%3E%3C%2FP%3E%0A%3CPRE%3Econn.AccessToken%20%3D%20%22%26lt%3Byour%20token%26gt%3B%22%3B%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1783117%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1783117%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20would%20be%20great%20if%20tools%20like%20SQLPackage%20or%20Invoke-SqlCmd%20could%20handle%20Service%20Principals%20as%20credentials%20(key%20or%20certificate)%2C%20especially%20for%20deployment%20scenarios%20in%20Azure%20DevOps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20of%20today%2C%20the%20only%20way%20I%20have%20found%20to%20do%20this%20is%20deploying%20all%20that%20is%20not%20related%20to%20AAD%20using%20the%20DacPac%20file%20under%20an%20SQL%20account%20and%20after%20then%20create%20users%20using%20this%20kind%20of%20trick.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20I%20am%20missing%20something%20but%20it%20is%20an%20important%20feature%20that%20is%20absent%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1785302%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1785302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F833420%22%20target%3D%22_blank%22%3E%40odelmotte%3C%2FA%3E%26nbsp%3BYou%20can%20use%20plain%20SqlConnection%2FSqlCommand%20to%20run%20SQL%20script%20with%20service%20principal%2C%20however%20in%20such%20case%20you%20can%20only%20use%20'normal'%20SQL%20syntax%2C%20and%20you%20cannot%20have%20'GO'%20keyword%20in%20your%20script%20since%20it%20is%20a%20special%20syntax%20that%20only%20SQLCMD%20understands.%20If%20you%20need%20to%20use%20'GO'%20keyword%2C%20you%20can%20still%20use%20SQL%20Server%20Management%20classes%20in%20Powershell.%20The%20example%20below%20should%20help%20(you%20of%20course%20need%20to%20provide%20all%20required%20variables).%20Note%20we%20use%20some%20Azure%20CLI%20commands%20there%20for%20simplicity.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EImport-Module%20-Name%20SqlServer%0A%0A%23%20Use%20Azure%20CLI%20to%20get%20token%2C%20much%20easier%20than%20using%20Powershell%20for%20that%0Aaz%20login%20--service-principal%20-u%20%24AppId%20-p%20%24Secret%20--tenant%20%24TenantId%20--allow-no-subscriptions%20%7C%20Out-Null%0A%24accessToken%20%3D%20az%20account%20get-access-token%20--resource%20https%3A%2F%2Fdatabase.windows.net%2F%20--query%20%22accessToken%22%20--output%20tsv%0A%24connectionString%20%3D%20%22Server%3Dtcp%3A%24ServerName%2C1433%3BInitial%20Catalog%3D%24DatabaseName%3BPersist%20Security%20Info%3DFalse%3BMultipleActiveResultSets%3DFalse%3BEncrypt%3DTrue%3BTrustServerCertificate%3DFalse%3B%22%20%0A%0Atry%20%7B%0A%20%20%20%23%20Create%20the%20connection%20object%20%0A%20%20%20%24connection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection(%24connectionString)%0A%20%20%20%24connection.AccessToken%20%3D%20%24accessToken%0A%0A%20%20%20%23%20Opens%20connection%20to%20Azure%20SQL%20Database%20and%20return%20connection%0A%20%20%20%24connection.Open()%20%0A%0A%20%20%20%23%20Running%20query%20with%20SQL%20SMO%20library%20rather%20than%20plain%20SqlCommand%20because%20our%20scripts%20contain%0A%20%20%20%23%20multiple%20batches%20separated%20with%20'GO'%20keyword%2C%20which%20is%20not%20allowed%20normally%20in%20SqlCommand.%20More%20info%3A%0A%20%20%20%23%20https%3A%2F%2Fsmehrozalam.wordpress.com%2F2009%2F05%2F12%2Fc-executing-batch-t-sql-scripts-with-go-statements%2F%0A%20%20%20%24svrConnection%20%3D%20New-Object%20Microsoft.SqlServer.Management.Common.ServerConnection(%24connection)%0A%20%20%20%24server%20%3D%20New-Object%20Microsoft.SqlServer.Management.Smo.Server(%24svrConnection)%0A%20%20%20%24server.ConnectionContext.ExecuteNonQuery(%24CmdText)%0A%20%20%20%24connection.Close()%0A%7D%20catch%20%7B%0A%20%20%20Write-Error%20%24_.Exception.ToString()%0A%20%20%20throw%0A%7D%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1785354%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1785354%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20this%20an%20option%20for%20scripts%20but%20what%20about%20manipulating%20DacPac%20files%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20working%20on%20many%20Azure%20Pipelines%20and%20I%20am%20stuck%20with%20SQL%20credentials%20to%20do%20the%20deployment%20part%20and%20the%20security%20is%20handled%20using%20something%20else.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20deploying%20to%20Azure%2C%20I%20would%20like%20to%20rely%20on%20the%20ARM%20connection%20I%20provide%20to%20the%20task%20to%20handle%20the%20login%20and%20then%20the%20deployment%20of%20the%20DacPac%20with%20my%20AAD%20related%20stuff.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786774%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786774%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F833420%22%20target%3D%22_blank%22%3E%40odelmotte%3C%2FA%3E%26nbsp%3Bplease%20shoot%20an%20email%20to%26nbsp%3B%3CA%20href%3D%22mailto%3ASQLAADFeedback%40microsoft.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESQLAADFeedback%40microsoft.com%3C%2FA%3E%26nbsp%3Bwith%20all%20the%20details%20and%20scenarios.%20As%20needed%2C%20we%20will%20involve%20more%20teams.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792265%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792265%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F680552%22%20target%3D%22_blank%22%3E%40AmolAgarwalSQL%3C%2FA%3E%26nbsp%3Bwill%20do%20next%20week.%20thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1835215%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1835215%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20have%20pushed%20a%20PR%20on%20Azure%20Pipelines%20Tasks%20to%20handle%20SP%20connections%20%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fmicrosoft%2Fazure-pipelines-tasks%2Fpull%2F13770%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2Fmicrosoft%2Fazure-pipelines-tasks%2Fpull%2F13770%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorking%20on%20making%20the%20CI%20pass%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481467%22%20slang%3D%22en-US%22%3EAzure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481467%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20following%20application%20provides%20an%20example%20of%20using%20Azure%20AD%20Service%20Principal%20(SP)%20to%20authenticate%20and%20connect%20to%20Azure%20SQL%20database.%20This%20application%20measures%20the%20time%20it%20takes%20to%20obtain%20an%20access%20token%2C%20total%20time%20it%20takes%20to%20establish%20a%20connection%2C%20and%20time%20it%20takes%20to%20run%20a%20query.%20The%20application%20can%20be%20used%20to%20troubleshoot%20delays%20during%20each%20phase%20of%20the%20connection%20and%20query%20process.%20In%20addition%2C%20this%20code%20sample%20can%20display%20the%20content%20of%20the%20access%20token%20obtained%20using%20Azure%20AD%20SP%20authentication.%20Please%20note%20that%20the%20same%20authentication%20mechanism%20applies%20to%20Managed%20Instance%20and%20SQL%20DW.%3C%2FP%3E%0A%3CP%3EFor%20more%20information%20on%20how%20to%20enable%20Azure%20AD%20authentication%20for%20SQL%20DB%20see%20%26nbsp%3B%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fen-us%2Fdocumentation%2Farticles%2Fsql-database-aad-authentication%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fazure.microsoft.com%2Fen-us%2Fdocumentation%2Farticles%2Fsql-database-aad-authentication%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EBefore%20building%20and%20running%20the%20code%20sample%2C%20perform%20the%20following%20steps%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%20style%3D%22text-align%3A%20left%3B%22%3ECreate%20a%20Service%20Principal%20in%20Azure%20AD%20for%20your%20service%20and%20obtained%20the%20following%20information%20required%20to%20execute%20the%20code%20sample%20below%3CBR%20%2F%3Ea.%26nbsp%3B%20Application%20ID%20of%20the%20Service%20Principal%20(SP)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20clientId%20%3D%20%22%3CAPPID%3E%22%3B%20%2F%2F%20Application%20ID%20of%20the%20SP%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20(e.g.%20string%20clientId%20%3D%20%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%22%3B)%3CBR%20%2F%3Eb.%20Copy%20the%20%E2%80%9CDisplay%20Name%E2%80%9D%20of%20your%20application%20which%20will%20be%20used%20in%20step%203)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20(e.g.%E2%80%9Ddebugapp%E2%80%9D%20as%20a%20%E2%80%9CDisplay%20Name%E2%80%9D%20for%20the%20app%20above)%3CBR%20%2F%3Ec.%26nbsp%3B%20Azure%20AD%20tenant%20ID%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20aadTenantId%20%3D%20%22%3CTENANTID%3E%22%3B%20%2F%2FAzure%20AD%20tenant%20ID%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20(e.g.%20string%20aadTenantId%20%3D%20%22xxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxxx%22%3B)%3CBR%20%2F%3Ed.%26nbsp%3B%20Client%20(SP)%20secret%20key%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20clientSecretKey%20%3D%20%22secretKey%22%3B%20%2F%2F%20Application%20secret%20key%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20(e.g.%20string%20clientSecretKey%20%3D%20%22xxxxxx%2Fxxxxxxxxxxxxxxxxxxxxxxxxxx%2Fxxx%22%3B)%3CBR%20%2F%3ETo%20obtain%20above%20information%20follow%20the%20steps%20indicated%20in%20the%20link%20below%20%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fdevelop%2Fhowto-create-service-principal-portal%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fdevelop%2Fhowto-create-service-principal-portal%3C%2FA%3E%3C%2FTENANTID%3E%3C%2FAPPID%3E%3C%2FLI%3E%0A%3CLI%3EFind%20the%20Azure%20SQL%20Server%20name%20and%20Database%20name%3CBR%20%2F%3EserverName%20%3D%20%22%3CSERVERNAME%3E%22%3B%20%2F%2F%20server%20name%3A%20myserver.database.windows.net%3CBR%20%2F%3E(e.g.%20string%20serverName%20%3D%20%22myserver.database.windows.net%22%3B)%3CBR%20%2F%3EdatabaseName%20%3D%20%22%3CDATABASENAME%3E%22%20%2F%2F%20database%20name%3A%20test%3B%3CBR%20%2F%3E(e.g.%20string%20databaseName%20%3D%20%22test%22%3B)%3C%2FDATABASENAME%3E%3C%2FSERVERNAME%3E%3C%2FLI%3E%0A%3CLI%3EUsing%20SSMS%20to%20connect%20to%20SQL%20DB%20(e.g.%20%E2%80%9Ctest%E2%80%9D)%20as%20an%20Azure%20AD%20user%20with%20proper%20Azure%20AD%20permissions%20(e.g.%20Azure%20AD%20admin%20for%20SQL%20DB)%2C%20create%20an%20application%20user%20from%20step%201%20above.%20Execute%20the%20T-SQL%20statement%20create%20user%20command%20%E2%80%9Ccreate%20user%20%5Bapp%20display%20name%5D%20from%20external%20provider%E2%80%9D.%20%3CBR%20%2F%3EExample%20using%20%E2%80%9Cdebugapp%E2%80%9D%20as%20a%20display%20name%20form%20step1%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20create%20user%20%5Bdebugapp%5D%20from%20external%20provider.%3CBR%20%2F%3ENote%20that%20the%20create%20user%20command%20grants%20this%20user%20a%20connect%20permission%20to%20the%20database%2C%20which%20is%20sufficient%20enough%20to%20execute%20the%20sample%20program%20below.%3C%2FLI%3E%0A%3CLI%3ECopy%20and%20execute%20the%20program%20indicated%20below%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EBelow%20is%20an%20example%20of%20the%20program%20output.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22debugg.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110031iFA7DD69B17253DFC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22debugg.png%22%20alt%3D%22debugg.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3EPlease%20note%20that%20the%20token%20information%20displaying%20the%20access%20token%20was%20commented%20out%20in%20the%20program%20output%3CBR%20%2F%3E%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3E%2F%2FDisplay%20a%20token%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3E%3CSTRONG%3E%2F%2FConsole.WriteLine(%22This%20is%20your%20token%3A%20%22%20%2B%20authenticationResult.AccessToken)%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EIf%20a%20token%20display%20is%20enabled%20(as%20it%20is%20in%20the%20program%20below)%2C%20it%20can%20be%20copied%20and%20decoded%20%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%20into%20a%20readable%20form%20with%20claims%2C%20%3C%2FSPAN%3Eusing%20%3CA%20href%3D%22https%3A%2F%2Fjwt.ms%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjwt.ms%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3EBelow%20is%20a%20C%23%20version%20of%20the%20application%20called%20Program.cs%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3ETo%20obtain%20the%20nuget%20package%20%E2%80%9CMicrosoft.IdentityModel.Clients.ActiveDirectory%E2%80%9D%2C%20use%20the%20link%20below%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3E%3CA%20style%3D%22background-color%3A%20transparent%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23146cac%3B%20text-decoration%3A%20underline%3B%22%20href%3D%22https%3A%2F%2Fwww.nuget.org%2Fapi%2Fv2%2Fpackage%2FMicrosoft.IdentityModel.Clients.ActiveDirectory%2F4.5.1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.nuget.org%2Fapi%2Fv2%2Fpackage%2FMicrosoft.IdentityModel.Clients.ActiveDirectory%2F4.5.1%3C%2FA%3E.%3C%2FP%3E%0A%3CPRE%3Eusing%20Microsoft.IdentityModel.Clients.ActiveDirectory%3B%0Ausing%20System%3B%0Ausing%20System.Collections.Generic%3B%0Ausing%20System.Data.SqlClient%3B%0Ausing%20System.Linq%3B%0Ausing%20System.Text%3B%0Ausing%20System.Threading.Tasks%3B%0A%0Anamespace%20AADTest%0A%7B%0A%20%20%20%20class%20Program%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20static%20void%20Main(string%5B%5D%20args)%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20Examples%20for%20the%20input%20parameters%0A%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20string%20serverName%20%3D%20%22%26lt%3BserverName%26gt%3B%22%3B%20%2F%2F%20server%20name%20i.e.%20sqlxx.database.windows.net%0A%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20string%20databaseName%20%3D%20%22%26lt%3BdatabaseName%26gt%3B%22%3B%20%2F%2FDatabase%20name%20%20i.e.%20test%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20string%20clientId%20%3D%20%22%26lt%3BappId%26gt%3B%22%3B%20%2F%2F%20application%20id%20of%20the%20service%20principal%0A%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20string%20aadTenantId%20%3D%20%22%26lt%3BtenantId%26gt%3B%22%3B%20%2F%2FAAD%20tenant%20id%0A%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20string%20clientSecretKey%20%3D%20%22secretKey%22%3B%20%2F%2F%20AAD%20app%20secret%20key%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20string%20serverName%20%3D%20%22myserver.database.windows.net%22%3B%20%0A%20%20%20%20%20%20%20%20%20%20%20%20string%20databaseName%20%3D%20%22test%22%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20string%20clientId%20%3D%20%22xxxxxx-xxxxx-xxxxx-xxxx-xxxx%22%3B%20%0A%20%20%20%20%20%20%20%20%20%20%20%20string%20aadTenantId%20%3D%20%22xxxxxx-xxxxxx-xxxxxx-xxxxxx-xxxxxxxx%22%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20string%20clientSecretKey%20%3D%20%22xxxxx%2Fxxxxxx%2Fxxxxx%22%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20string%20sqlConnectionString%20%3D%20String.Format(%22Data%20Source%3Dtcp%3A%7B0%7D%2C1433%3BInitial%20Catalog%3D%7B1%7D%3BPersist%20Security%20Info%3DFalse%3BConnect%20Timeout%3D30%3BEncrypt%3DTrue%3BTrustServerCertificate%3DFalse%22%2C%20serverName%2C%20databaseName)%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20string%20AadInstance%20%3D%20%22https%3A%2F%2Flogin.windows.net%2F%7B0%7D%22%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20string%20ResourceId%20%3D%20%22https%3A%2F%2Fdatabase.windows.net%2F%22%3B%0A%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20AuthenticationContext%20authenticationContext%20%3D%20new%20AuthenticationContext(string.Format(AadInstance%2C%20aadTenantId))%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20ClientCredential%20clientCredential%20%3D%20new%20ClientCredential(clientId%2C%20clientSecretKey)%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20DateTime%20startTime%20%3D%20DateTime.Now%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Time%20%22%20%2B%20String.Format(%22%7B0%3Amm%3Ass.fff%7D%22%2C%20startTime))%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20AuthenticationResult%20authenticationResult%20%3D%20authenticationContext.AcquireTokenAsync(ResourceId%2C%20clientCredential).Result%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20DateTime%20endTime%20%3D%20DateTime.Now%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Got%20token%20at%20%22%20%2B%20String.Format(%22%7B0%3Amm%3Ass.fff%7D%22%2C%20endTime))%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Total%20time%20to%20get%20token%20in%20milliseconds%20%22%20%2B%20(endTime%20-%20startTime).TotalMilliseconds)%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20using%20(var%20conn%20%3D%20new%20SqlConnection(sqlConnectionString))%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20conn.AccessToken%20%3D%20authenticationResult.AccessToken%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20startTime%20%3D%20DateTime.Now%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Starting%20to%20open%20connection%20at%20%22%20%2B%20String.Format(%22%7B0%3Amm%3Ass.fff%7D%22%2C%20startTime))%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2FDisplay%20a%20token%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22This%20is%20your%20token%3A%20%22%20%2B%20authenticationResult.AccessToken)%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20conn.Open()%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20endTime%20%3D%20DateTime.Now%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Got%20connection%20at%20%22%20%2B%20String.Format(%22%7B0%3Amm%3Ass.fff%7D%22%2C%20endTime))%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Total%20time%20to%20establish%20connection%20in%20milliseconds%20%22%20%2B%20(endTime%20-%20startTime).TotalMilliseconds)%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20startTime%20%3D%20DateTime.Now%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Starting%20to%20run%20query%20at%20%22%20%2B%20String.Format(%22%7B0%3Amm%3Ass.fff%7D%22%2C%20startTime))%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20using%20(var%20cmd%20%3D%20new%20SqlCommand(%22SELECT%201%22%2C%20conn))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20var%20result%20%3D%20cmd.ExecuteScalar()%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(result.ToString())%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20endTime%20%3D%20DateTime.Now%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Completing%20running%20query%20at%20%22%20%2B%20String.Format(%22%7B0%3Amm%3Ass.fff%7D%22%2C%20endTime))%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Console.WriteLine(%22Total%20time%20to%20execute%20query%20in%20milliseconds%20%22%20%2B%20(endTime%20-%20startTime).TotalMilliseconds)%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20Console.ReadKey()%3B%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-481467%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-481467%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Security%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502475%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20AD%20Service%20Principal%20authentication%20to%20SQL%20DB%20-%20Code%20Sample%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502475%22%20slang%3D%22en-US%22%3E%3CP%3EUse%20this%20when%20there%20is%20need%20to%20get%20token%20for%20user.%20This%20will%20be%20interactive%20though.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EFrom%20PowerShell%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EInstall-Module%20-Name%20MSAL.PS%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Erestart%20powershell.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%24authority%20%3D%20%22%3CA%20href%3D%22https%3A%2F%2Flogin.microsoftonline.com%2Fcommon%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Flogin.microsoftonline.com%2Fcommon%3C%2FA%3E%22%3CBR%20%2F%3E%23this%20is%20the%20security%20and%20compliance%20center%20endpoint%3CBR%20%2F%3E%24resourceUrl%20%3D%20%22%3CA%20href%3D%22https%3A%2F%2Fdatabase.windows.net%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdatabase.windows.net%2F%3C%2FA%3E%22%3CBR%20%2F%3E%24clientId%20%3D%20%221950a258-227b-4e31-a9cf-717495945fc2%22%20%23%20known%20powershell%20client%20id%3C%2FP%3E%0A%3CP%3E%24redirectUri%20%3D%20%22urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob%22%3C%2FP%3E%0A%3CP%3EGet-MsalToken%20-ClientId%20%24clientId%20-RedirectUri%20%24redirectUri%20%3CBR%20%2F%3E%24response.AccessToken%20%7C%20clip%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EFrom%20C%23%20program%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3Estring%20clientId%20%3D%20%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%22%20%2F%2F%20known%20powershell%20client%20id%3CBR%20%2F%3Estring%20aadTenantId%20%3D%20%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%22%3B%3CBR%20%2F%3E%3CBR%20%2F%3Estring%20ResourceId%20%3D%20%22%3CA%20href%3D%22https%3A%2F%2Fdatabase.windows.net%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdatabase.windows.net%2F%3C%2FA%3E%22%3B%3CBR%20%2F%3Estring%20AadInstance%20%3D%20%22%3CA%20href%3D%22https%3A%2F%2Flogin.windows.net%2F%7B0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Flogin.windows.net%2F%7B0%3C%2FA%3E%7D%22%3B%3C%2FP%3E%0A%3CP%3EAuthenticationContext%20authenticationContext%20%3D%20new%20AuthenticationContext(string.Format(AadInstance%2C%20aadTenantId))%3B%3C%2FP%3E%0A%3CP%3Evar%20user%20%3D%20new%20UserCredential(%22myalias%22)%3B%3C%2FP%3E%0A%3CP%3EAuthenticationResult%20authResult%20%3D%20authenticationContext.AcquireTokenAsync(ResourceId%2C%20clientId%2C%20user).Result%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E(Edit%3A%20client%20IDs%20and%20tenant%20ID%20removed%20from%20code%20example%20%2Fcc%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F680552%22%20target%3D%22_blank%22%3E%40AmolAgarwalSQL%3C%2FA%3E)%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

The following application provides an example of using Azure AD Service Principal (SP) to authenticate and connect to Azure SQL database. This application measures the time it takes to obtain an access token, total time it takes to establish a connection, and time it takes to run a query. The application can be used to troubleshoot delays during each phase of the connection and query process. In addition, this code sample can display the content of the access token obtained using Azure AD SP authentication. Please note that the same authentication mechanism applies to Managed Instance and SQL DW.

For more information on how to enable Azure AD authentication for SQL DB see  
https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/


Before building and running the code sample, perform the following steps:

  1. Create a Service Principal in Azure AD for your service and obtained the following information required to execute the code sample below
    a.  Application ID of the Service Principal (SP)
        clientId = "<appId>"; // Application ID of the SP
        (e.g. string clientId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";)
    b. Copy the “Display Name” of your application which will be used in step 3)
       (e.g.”debugapp” as a “Display Name” for the app above)
    c.  Azure AD tenant ID
         aadTenantId = "<tenantId>"; //Azure AD tenant ID
         (e.g. string aadTenantId = "xxxxxxx-xxxxx-xxxx-xxxx-xxxxxxxxxxxx";)
    d.  Client (SP) secret key
         clientSecretKey = "secretKey"; // Application secret key 
         (e.g. string clientSecretKey = "xxxxxx/xxxxxxxxxxxxxxxxxxxxxxxxxx/xxx";)
    To obtain above information follow the steps indicated in the link below
    https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-porta...
  2. Find the Azure SQL Server name and Database name
    serverName = "<serverName>"; // server name: myserver.database.windows.net
    (e.g. string serverName = "myserver.database.windows.net";)
    databaseName = "<databaseName>" // database name: test;
    (e.g. string databaseName = "test";)
  3. Using SSMS to connect to SQL DB (e.g. “test”) as an Azure AD user with proper Azure AD permissions (e.g. Azure AD admin for SQL DB), create an application user from step 1 above. Execute the T-SQL statement create user command “create user [app display name] from external provider”.
    Example using “debugapp” as a display name form step1
        create user [debugapp] from external provider.
    Note that the create user command grants this user a connect permission to the database, which is sufficient enough to execute the sample program below.
  4. Copy and execute the program indicated below

Below is an example of the program output.

debugg.png

 

 

Please note that the token information displaying the access token was commented out in the program output
//Display a token

//Console.WriteLine("This is your token: " + authenticationResult.AccessToken);

If a token display is enabled (as it is in the program below), it can be copied and decoded into a readable form with claims, using https://jwt.ms/

.

Below is a C# version of the application called Program.cs

To obtain the nuget package “Microsoft.IdentityModel.Clients.ActiveDirectory”, use the link below

https://www.nuget.org/api/v2/package/Microsoft.IdentityModel.Clients.ActiveDirectory/4.5.1.

using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AADTest
{
    class Program
    {
        static void Main(string[] args)
        {
         
            // Examples for the input parameters
            // string serverName = "<serverName>"; // server name i.e. sqlxx.database.windows.net
            // string databaseName = "<databaseName>"; //Database name  i.e. test 
            // string clientId = "<appId>"; // application id of the service principal
            // string aadTenantId = "<tenantId>"; //AAD tenant id
            // string clientSecretKey = "secretKey"; // AAD app secret key

            string serverName = "myserver.database.windows.net"; 
            string databaseName = "test";
            string clientId = "xxxxxx-xxxxx-xxxxx-xxxx-xxxx"; 
            string aadTenantId = "xxxxxx-xxxxxx-xxxxxx-xxxxxx-xxxxxxxx";
            string clientSecretKey = "xxxxx/xxxxxx/xxxxx";

            string sqlConnectionString = String.Format("Data Source=tcp:{0},1433;Initial Catalog={1};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False", serverName, databaseName);

            string AadInstance = "https://login.windows.net/{0}";
            string ResourceId = "https://database.windows.net/";


            AuthenticationContext authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));
            ClientCredential clientCredential = new ClientCredential(clientId, clientSecretKey);

            DateTime startTime = DateTime.Now;
            Console.WriteLine("Time " + String.Format("{0:mm:ss.fff}", startTime));

            AuthenticationResult authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result;

            DateTime endTime = DateTime.Now;
            Console.WriteLine("Got token at " + String.Format("{0:mm:ss.fff}", endTime));

            Console.WriteLine("Total time to get token in milliseconds " + (endTime - startTime).TotalMilliseconds);

            using (var conn = new SqlConnection(sqlConnectionString))
            {
                conn.AccessToken = authenticationResult.AccessToken;

                startTime = DateTime.Now;
                Console.WriteLine("Starting to open connection at " + String.Format("{0:mm:ss.fff}", startTime));

                //Display a token
                Console.WriteLine("This is your token: " + authenticationResult.AccessToken);

                conn.Open();

                endTime = DateTime.Now;
                Console.WriteLine("Got connection at " + String.Format("{0:mm:ss.fff}", endTime));

                Console.WriteLine("Total time to establish connection in milliseconds " + (endTime - startTime).TotalMilliseconds);

                startTime = DateTime.Now;
                Console.WriteLine("Starting to run query at " + String.Format("{0:mm:ss.fff}", startTime));

                using (var cmd = new SqlCommand("SELECT 1", conn))
                {
                    var result = cmd.ExecuteScalar();
                    Console.WriteLine(result.ToString());
                }

                endTime = DateTime.Now;
                Console.WriteLine("Completing running query at " + String.Format("{0:mm:ss.fff}", endTime));
                Console.WriteLine("Total time to execute query in milliseconds " + (endTime - startTime).TotalMilliseconds);
            }

            Console.ReadKey();
        }
    }
}

 

18 Comments
Occasional Visitor

How to login to the test database using the SSMS and not through code? Is this even supported? If yes, What would be the option to choose for authentication to sign in and is the secret key the password? 

Occasional Visitor

@jnprakash You can not login to the Azure SQL with SSMS using the service principal as I know 

https://dba.stackexchange.com/questions/184598/unable-to-connect-using-azure-ad-service-principal-on...

Occasional Visitor

@Mirek Sztajno Very good tutorial, but is there a way to run 3 step automatically in CI/CD? with Powershell or Azure Devops or somehow else but automatically = without SSMS?

Microsoft

@jnprakash At this point we do not have this option in our client tools like SSMS. I will update once we have added this support. 

Microsoft

@R-Chaser , there are some possibilities, depending on your scenario. Can you please email us at  SQLAADAuth@microsoft.com and we can discuss the options with you. 

Frequent Visitor

@AmolAgarwalSQL I have the same issue as @R-Chaser and already wrote to the mailing address you mentioned but I haven't got any response.

 

Could you share some feedback here how to automate this process in Azure Pipelines when it is NOT possible to create external (AD) SQL users when signed in as Service Principal?

Microsoft

Please contact SQLAADFeedback@microsoft.com alias to discuss the details 

Microsoft

@Mirek Sztajno  can you please reach out to @R-Chaser and discuss options with him. 

Microsoft

Use this when there is need to get token for user. This will be interactive though. 

From PowerShell

Install-Module -Name MSAL.PS

 

restart powershell.

 

$authority = "https://login.microsoftonline.com/common"
#this is the security and compliance center endpoint
$resourceUrl = "https://database.windows.net/"
$clientId = "1950a258-227b-4e31-a9cf-717495945fc2" # known powershell client id

$redirectUri = "urn:ietf:wg:oauth:2.0:oob"

Get-MsalToken -ClientId $clientId -RedirectUri $redirectUri
$response.AccessToken | clip

 

From C# program

string clientId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" // known powershell client id
string aadTenantId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";

string ResourceId = "https://database.windows.net/";
string AadInstance = "https://login.windows.net/{0}";

AuthenticationContext authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));

var user = new UserCredential("myalias");

AuthenticationResult authResult = authenticationContext.AcquireTokenAsync(ResourceId, clientId, user).Result

 

 

(Edit: client IDs and tenant ID removed from code example /cc @AmolAgarwalSQL)

Microsoft

When we deployed the production, there is no client secret key anymore.  How will the example work without a client secret key?  Do we just leave that blank?

 

<Amol> This will not work without a secret key, how is your app getting a token, Once you have a token, you can directly put it here  and then connect. Token will start with something like ey....

conn.AccessToken = "<your token>";

 

Occasional Visitor

What is the approach for handling expiration of token? We have a spring boot app in our AKS and it's restarting every hour because the AAD token to connect to AZURE SQL expires every hour.

Microsoft

@MarcusDNguyen , if token is expired, client has to get a new token from AAD and connect. So, a retry logic is needed which can detect that token has expired and gets a new token. 

Below is the link from AAD on token lifetime. 

https://docs.microsoft.com/en-us/azure/active-directory-b2c/tokens-overview#:~:text=Access%20%26%20I...

Senior Member

It would be great if tools like SQLPackage or Invoke-SqlCmd could handle Service Principals as credentials (key or certificate), especially for deployment scenarios in Azure DevOps

 

As of today, the only way I have found to do this is deploying all that is not related to AAD using the DacPac file under an SQL account and after then create users using this kind of trick.

 

Maybe I am missing something but it is an important feature that is absent

Frequent Visitor

@odelmotte You can use plain SqlConnection/SqlCommand to run SQL script with service principal, however in such case you can only use 'normal' SQL syntax, and you cannot have 'GO' keyword in your script since it is a special syntax that only SQLCMD understands. If you need to use 'GO' keyword, you can still use SQL Server Management classes in Powershell. The example below should help (you of course need to provide all required variables). Note we use some Azure CLI commands there for simplicity.

 

Import-Module -Name SqlServer

# Use Azure CLI to get token, much easier than using Powershell for that
az login --service-principal -u $AppId -p $Secret --tenant $TenantId --allow-no-subscriptions | Out-Null
$accessToken = az account get-access-token --resource https://database.windows.net/ --query "accessToken" --output tsv
$connectionString = "Server=tcp:$ServerName,1433;Initial Catalog=$DatabaseName;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;" 

try {
   # Create the connection object 
   $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
   $connection.AccessToken = $accessToken

   # Opens connection to Azure SQL Database and return connection
   $connection.Open() 

   # Running query with SQL SMO library rather than plain SqlCommand because our scripts contain
   # multiple batches separated with 'GO' keyword, which is not allowed normally in SqlCommand. More info:
   # https://smehrozalam.wordpress.com/2009/05/12/c-executing-batch-t-sql-scripts-with-go-statements/
   $svrConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($connection)
   $server = New-Object Microsoft.SqlServer.Management.Smo.Server($svrConnection)
   $server.ConnectionContext.ExecuteNonQuery($CmdText)
   $connection.Close()
} catch {
   Write-Error $_.Exception.ToString()
   throw
}
Senior Member

Yes, this an option for scripts but what about manipulating DacPac files

 

I have been working on many Azure Pipelines and I am stuck with SQL credentials to do the deployment part and the security is handled using something else.

 

When deploying to Azure, I would like to rely on the ARM connection I provide to the task to handle the login and then the deployment of the DacPac with my AAD related stuff.

Microsoft

@odelmotte please shoot an email to SQLAADFeedback@microsoft.com with all the details and scenarios. As needed, we will involve more teams. 

Senior Member

@AmolAgarwalSQL will do next week. thank you

Senior Member

Hi, have pushed a PR on Azure Pipelines Tasks to handle SP connections : https://github.com/microsoft/azure-pipelines-tasks/pull/13770 

Working on making the CI pass