Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1007112%22%20slang%3D%22en-US%22%3EAn%20example%20of%20SQL%20Linux%20Windows%20Authentication%20configuration%20using%20Managed%20Service%20Accounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1007112%22%20slang%3D%22en-US%22%3E%3CP%3EEnvironment%3C%2FP%3E%0A%3CP%3E%3D%3D%3D%3C%2FP%3E%0A%3CP%3EDomain%3Acontoso.com%3C%2FP%3E%0A%3CP%3ELinux%20server%3Ared1(Redhat%207.6)%3C%2FP%3E%0A%3CP%3EMSA%3Amsa01%3C%2FP%3E%0A%3CP%3ESQL%20Server%202017%20CU17%3C%2FP%3E%0A%3CP%3ERed1%20has%20joined%20the%20domain%20contoso.com(For%20information%20on%20how%20to%20join%20an%20active%20directory%20domain%2C%20see%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fsql-server-linux-active-directory-join-domain%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EJoin%20SQL%20Server%20on%20a%20Linux%20host%20to%20an%20Active%20Directory%20domain%3C%2FA%3E.)%3C%2FP%3E%0A%3CP%3E%3CEM%3E%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ECreate%20a%20MSA%20account%20with%20password%20specified%20in%20Powershell%20command%20prompt.%20Please%20note%2C%20the%20password%20of%20MSA%20should%20be%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3ENew-ADServiceAccount%20-Name%20msa01%20-Enabled%20%24true%26nbsp%3B%26nbsp%3B%20-AccountPassword%20(Read-Host%20-AsSecureString%20%22Enter%20Password%22)%20-RestrictToSingleComputer%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157017i11B4AE40E22A8AA0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_15.png%22%20title%3D%22clipboard_image_15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%222%22%3E%0A%3CLI%3EGet%20the%20KVNO%2C%20running%20following%20command%20in%20SQL%20Linux%20box%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Ekinit%20msa01%40CONTOSO.COM%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Ekvno%20MSSQLSvc%2Fred1.contoso.com%3A1433%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157016i9865A0CCC57BBED4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_16.png%22%20title%3D%22clipboard_image_16.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%223%22%3E%0A%3CLI%3E%3CSTRONG%3EAlternatively%3C%2FSTRONG%3E%2C%20you%20can%20run%20following%20Powershell%20command%20to%20get%20the%20KVNO%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Eget-ADServiceAccount%20-Identity%20msa01%20-property%20msDS-KeyVersionNumber%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157018iEBB1EF64395645F1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_17.png%22%20title%3D%22clipboard_image_17.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%224%22%3E%0A%3CLI%3ERun%20following%20bash%20commands%20in%20command%20prompt%20in%20windows%20server%20to%20prepare%20the%20mssql.keytab%20file.%20SPN%20entries%20will%20be%20created%20in%20this%20step.(Please%20replace%20the%20Password1%20with%20the%20password%20you%20specified%20in%20step1%2C%20and%20kvno%202%20got%20from%20step%202)%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CFONT%20size%3D%221%22%20color%3D%22%230000ff%22%3Ektpass%20%2Fprinc%20MSSQLSvc%2Fred1.CONTOSO.COM%3A1433%40CONTOSO.COM%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2Fptype%20KRB5_NT_PRINCIPAL%20%2Fcrypto%20aes256-sha1%20%2Fmapuser%20contoso%5Cmsa01%20%2Fout%20mssql.keytab%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-setpass%20-setupn%20%2Fkvno%202%20%2Fpass%20Password1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%221%22%20color%3D%22%230000ff%22%3Ektpass%20%2Fprinc%20MSSQLSvc%2Fred1%3A1433%40CONTOSO.COM%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2Fptype%20KRB5_NT_PRINCIPAL%20%2Fcrypto%20aes256-sha1%20%2Fmapuser%20contoso%5Cmsa01%26nbsp%3B%26nbsp%3B%20%2Fin%20mssql.keytab%20%2Fout%20mssql.keytab%20-setpass%20-setupn%20%2Fkvno%202%20%2Fpass%20Password1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%221%22%20color%3D%22%230000ff%22%3Ektpass%20%2Fprinc%20MSSQLSvc%2Fred1.CONTOSO.COM%3A1433%40CONTOSO.COM%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2Fptype%20KRB5_NT_PRINCIPAL%20%2Fcrypto%20rc4-hmac-nt%20%2Fmapuser%20contoso%5Cmsa01%26nbsp%3B%26nbsp%3B%20%2Fin%20mssql.keytab%20%2Fout%20mssql.keytab%20-setpass%20-setupn%20%2Fkvno%202%20%2Fpass%20Password1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%221%22%20color%3D%22%230000ff%22%3Ektpass%20%2Fprinc%20MSSQLSvc%2Fred1%3A1433%40CONTOSO.COM%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2Fptype%20KRB5_NT_PRINCIPAL%20%2Fcrypto%20rc4-hmac-nt%20%2Fmapuser%20contoso%5Cmsa01%26nbsp%3B%26nbsp%3B%20%2Fin%20mssql.keytab%20%2Fout%20mssql.keytab%20-setpass%20-setupn%20%2Fkvno%202%20%2Fpass%20Password1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%221%22%20color%3D%22%230000ff%22%3Ektpass%20%2Fprinc%20msa01%40CONTOSO.COM%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2Fptype%20KRB5_NT_PRINCIPAL%20%2Fcrypto%20aes256-sha1%20%2Fmapuser%20contoso%5Cmsa01%20%26nbsp%3B%26nbsp%3B%2Fin%20%26nbsp%3Bmssql.keytab%20%2Fout%20mssql.keytab%20%26nbsp%3B-setpass%20-setupn%20%2Fkvno%202%20%2Fpass%20Password1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%221%22%20color%3D%22%230000ff%22%3Ektpass%20%2Fprinc%20msa01%40CONTOSO.COM%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2Fptype%20KRB5_NT_PRINCIPAL%20%2Fcrypto%20rc4-hmac-nt%20%2Fmapuser%20contoso%5Cmsa01%20%26nbsp%3B%26nbsp%3B%2Fin%20%26nbsp%3Bmssql.keytab%20%2Fout%20mssql.keytab%20%26nbsp%3B-setpass%20-setupn%20%2Fkvno%202%20%2Fpass%20Password1%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%225%22%3E%0A%3CLI%3ECopy%20the%20file%20keytab%20to%20SQL%20Server%20Linux%20box%20and%20rename%20to%20mssql.keytab%20%2C%20put%20in%20folder%20%2Fvar%2Fopt%2Fmssql%2Fsecrets%2F%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157020i9577BD227D3AA434%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_18.png%22%20title%3D%22clipboard_image_18.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%226%22%3E%0A%3CLI%3EWhen%20using%20the%20MSA%20approach%2C%20a%20configuration%20option%20needs%20to%20be%20set%20with%20the%20mssql-conf%20tool%20to%20specify%20the%20MSA%20to%20be%20used%20while%20accessing%20the%20keytab%20file.%20Ensure%20the%20values%20below%20are%20in%20%2Fvar%2Fopt%2Fmssql%2Fmssql.conf.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Esudo%20%2Fopt%2Fmssql%2Fbin%2Fmssql-conf%20set%20network.privilegedadaccount%20msa01%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Esystemctl%20restart%20mssql-server.service%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157019iE2614AD3F15FD184%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_19.png%22%20title%3D%22clipboard_image_19.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%20start%3D%227%22%3E%0A%3CLI%3ESecure%20the%20keytab%20file%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Esudo%20chown%20mssql%3Amssql%20%2Fvar%2Fopt%2Fmssql%2Fsecrets%2Fmssql.keytab%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Esudo%20chmod%20400%20%2Fvar%2Fopt%2Fmssql%2Fsecrets%2Fmssql.keytab%3C%2FFONT%3E%3C%2FP%3E%0A%3COL%20start%3D%228%22%3E%0A%3CLI%3EConfigure%20SQL%20Server%20to%20use%20the%20keytab%20file%20for%20Kerberos%20authentication%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Esudo%20%2Fopt%2Fmssql%2Fbin%2Fmssql-conf%20set%20network.kerberoskeytabfile%20%2Fvar%2Fopt%2Fmssql%2Fsecrets%2Fmssql.keytab%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000ff%22%3Esudo%20systemctl%20restart%20mssql-server%3C%2FFONT%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%3EThere%20is%20a%20known%20issue%20in%20AES%20256%20encryption%20in%20krb5%20library%20in%20redhat%2Fcentos%20and%20Ubuntu.%20There%20has%20been%20a%20patch%20in%20krb5%20library%2C%20but%20the%20patch%20hasn't%20shipped%20on%20distributions%20we%20support.%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%3EIf%20SPN%20entries%20are%20added%20using%20%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3Eadden%3C%2FSTRONG%3E%2C%20you%20can't%20even%20connect%20to%20SQL%20Server%20using%20Windows%20Authentication.%20And%20you%20will%20see%20following%20messages%20in%20PALLOG.%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%3ERequest%20ticket%20server%20MSSQLSvc%2Fred1%3A1433%40CONTOSO.COM%20kvno%202%20enctype%20aes256-cts%20found%20in%20keytab%20but%20cannot%20decrypt%20ticket%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%3EIf%20MSA%20entries%20are%20added%20using%20adden%2C%20you%20will%20fail%20to%20run%20'create%20login'%2C'sp_addsrvrolemember'%2C%20or%20other%20privileged%20operation%20and%20get%20following%20message%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%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Could%20not%20obtain%20information%20about%20Windows%20NT%20group%2Fuser%20'%25ls'%2C%20error%20code%200x80090304%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%3EI%E2%80%99ll%20discuss%20the%20issue%20in%20other%20articles.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1007112%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22margin%3A%200in%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20579px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158539iEC5497810E57EB70%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20title%3D%22sql-loves-linux_2_twitter-002-640x358-100648879-large.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1007112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELinux%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Environment

===

Domain:contoso.com

Linux server:red1(Redhat 7.6)

MSA:msa01

SQL Server 2017 CU17

Red1 has joined the domain contoso.com(For information on how to join an active directory domain, see Join SQL Server on a Linux host to an Active Directory domain.)

 

 

  1. Create a MSA account with password specified in Powershell command prompt. Please note, the password of MSA should be

New-ADServiceAccount -Name msa01 -Enabled $true   -AccountPassword (Read-Host -AsSecureString "Enter Password") -RestrictToSingleComputer

clipboard_image_15.png

  1. Get the KVNO, running following command in SQL Linux box

kinit msa01@CONTOSO.COM

kvno MSSQLSvc/red1.contoso.com:1433    

clipboard_image_16.png

  1. Alternatively, you can run following Powershell command to get the KVNO

get-ADServiceAccount -Identity msa01 -property msDS-KeyVersionNumber

clipboard_image_17.png

  1. Run following bash commands in command prompt in windows server to prepare the mssql.keytab file. SPN entries will be created in this step.(Please replace the Password1 with the password you specified in step1, and kvno 2 got from step 2)

ktpass /princ MSSQLSvc/red1.CONTOSO.COM:1433@CONTOSO.COM         /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\msa01 /out mssql.keytab                   -setpass -setupn /kvno 2 /pass Password1

ktpass /princ MSSQLSvc/red1:1433@CONTOSO.COM                     /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\msa01   /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1

ktpass /princ MSSQLSvc/red1.CONTOSO.COM:1433@CONTOSO.COM       /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\msa01   /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1

ktpass /princ MSSQLSvc/red1:1433@CONTOSO.COM                     /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\msa01   /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1

ktpass /princ msa01@CONTOSO.COM        /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\msa01   /in  mssql.keytab /out mssql.keytab  -setpass -setupn /kvno 2 /pass Password1

ktpass /princ msa01@CONTOSO.COM        /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\msa01   /in  mssql.keytab /out mssql.keytab  -setpass -setupn /kvno 2 /pass Password1

 

  1. Copy the file keytab to SQL Server Linux box and rename to mssql.keytab , put in folder /var/opt/mssql/secrets/

clipboard_image_18.png

  1. When using the MSA approach, a configuration option needs to be set with the mssql-conf tool to specify the MSA to be used while accessing the keytab file. Ensure the values below are in /var/opt/mssql/mssql.conf.

sudo /opt/mssql/bin/mssql-conf set network.privilegedadaccount msa01

systemctl restart mssql-server.service

clipboard_image_19.png

  1. Secure the keytab file

sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab

sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab

  1. Configure SQL Server to use the keytab file for Kerberos authentication

sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab

sudo systemctl restart mssql-server

 

 

There is a known issue in AES 256 encryption in krb5 library in redhat/centos and Ubuntu. There has been a patch in krb5 library, but the patch hasn't shipped on distributions we support.

If SPN entries are added using adden, you can't even connect to SQL Server using Windows Authentication. And you will see following messages in PALLOG.

Request ticket server MSSQLSvc/red1:1433@CONTOSO.COM kvno 2 enctype aes256-cts found in keytab but cannot decrypt ticket

If MSA entries are added using adden, you will fail to run 'create login','sp_addsrvrolemember', or other privileged operation and get following message

           Could not obtain information about Windows NT group/user '%ls', error code 0x80090304

I’ll discuss the issue in other articles.