Add support for Kerberos/Active Directory/"windows" authentication

[EdiDD]

How to use windows authentication? Will assigning linux host to windows domain be enough for container to work with domain user authentication ? What can i do to achieve this ? I have windows server 2012 as active directory domain controller and debian 9 for docker.

[twright-msft]

AFAIK it is not possible to use AD authentication with Linux containers. There is a mechanism for using global managed service accounts for Windows containers, but we havent specifically tested that scenario yet.

[EdiDD]

Even with workaround like linking to openldap container or something like that ?

[twright-msft]

We havent tried it. Seems unlikely, but if you do give it a go, please let us know the results. I'm going to check into what it would take to get this lit up in Docker Engine in general with the Docker folks.

[thenktor]

So if I want to use AD authentication I cannot use the MSSQL docker image, but have to setup MSSQL on an Ubuntu/Redhat/Opensuse server like described here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-linux-2017

[twright-msft]

We are investigating how to do Kerberos/AD in a container, but yes, for now our recommendation is to not use containers if you need AD auth and to install directly on the host or VM OS using the docs you linked to above.

[zerg2k]

This was opened 7 months ago. Any news or progress?

[twright-msft]

We are starting to work on this in the next couple of weeks. We will update as we have information or bits to share.

[b9chris]

Is this possible now? We have a Sql Server instance on a Windows host we'd like to access from a Linux Docker container via Windows Authentication.

[labmonkey42]

It may be useful to clarify that there are two, fundamentally-different issues at play here.

1. A SQL Server on Linux instance running in a container needs an Active Directory trust mechanism in order to authenticate AD users from any client, whether that client is inside or outside of the container.
2. An application running inside a container and acting as a client using AD credentials to connect to a SQL Server instance (regardless of whether that instance is running in a container) with AD authentication enabled has to meet SQL Server's expectation of trust in order to authenticate those credentials.

In the first case, SQL Server needs to function with a SPN in a context where networking is ephemeral. I think that's what the Microsoft folks are working on with this issue.
In the second case, SQL Server does not need to know nor care whether the client is running in a container, and the use case is more about how apps in containers present themselves to the domain. This seems more like a Docker functionality matter.

Ultimately, a facility within Docker which allows apps within containers to use a SPN via host membership in a domain might answer both of these issues, but that's an upstream topic.

[b9chris]

So, no, it's not possible yet?

[twright-msft]

Still working on it...

[walliski]

@twright-msft

Hey, sorry for bothering with the same question, but do you have any kind of timeline for when this issue might be resolved? Are we talking weeks, months or a longer time?

[nadaraj15]

@twright-msft I want to install SSAS model on a docker container. And that container has to be domain joined and AD authenticated. Is it possible yet in windows or linux container image?

Any update on Kerberos/Active Directory/"windows" authentication ?

[twright-msft]

Not yet. Working on it...

[gavinsbtm]

@twright-msft

Is there any timescale to supporting this? Struggling with 3rd party apps using hardcoded Integrated Security in connection DB connection string.

[twright-msft]

I know everybody is excited about this one. Just to set expectations... we are working on it. It is planned to show up in SQL Server 2019 big data clusters first and then in SQL Server 2019 on Linux in general during the preview cycles. AD on Windows containers is further down the priority list as it is part of the overall effort to get SQL on Windows containers to production level quality. Once we have things working on SQL Server 2019, we will evaluate the demand vs effort required to take this down level to SQL Server 2017. In other words, production ready AD auth for containers is on the order of months out from now if not longer depending on whether or not you are willing to deploy SQL Server 2019 into production on Linux containers prior to general availability as part of the Early Adoption Program or not.

[turboaaa]

Not sure if this is helpful, but I was able to get sssd working with containers by binding to the host's ssd config. In my case I was converting an old freeradius google auth server to a docker container. After enrolling the Amazon Linux 2 instance into AD using sssd, I then mounted /var/lib/sss into the centos 7 container I was building.

I want to start using the Linux container, but AD auth would be a requirement to keep the business analyst happy.

[anchitanc]

is there any workaround to use SASS with container ?

[esauser]

@twright-msft is there any documentation on how to set this up for the 2019 containers?

[twright-msft]

@esauser - Assuming you mean the SQL Server 2019 on Linux container images, please see here: https://hub.docker.com/r/microsoft/mssql-server The pull/run/configure is all basically the same as SQL Server 2017 containers with two exceptions:

1. The tag you pull needs to be something like this 2019-CTP2.2-ubuntu (most recent CTP tag at the time I am writing this comment).
2. The registry you pull from needs to be mcr.microsoft.com/mssql/server

i.e. your pull command should look like this:
docker pull mcr.microsoft.com/mssql/server:2019-CTP2.2-ubuntu

Other than that the env vars, etc. are all the same between the 2017 and 2019 images.

[esauser]

@twright-msft I meant documentation specifically related to this issue. You had noted above that this would be possible in the 2019 docker images, but I can't find any documentation on how to go about the setup.

[twright-msft]

@esauser - Ah, understood. It's not ready for testing just yet. We'll come back around and update this issue when it is ready.

[PrometheusRising1]

Since BDC on Openshift now supports Windows Auth, has there been any progress on this?

[vicrem]

Hi everyone,

I think I managed to get a working solution where we dont need a domain joined container or host to use Windows Authentication.

The solution is far from automated-> You'll need to create a keytab, TLS cert and configure mssql.conf outside the build, or at least that's how I did it..

Anyway.. My setup:

Docker Swarm with 3 nodes (none of them are domain joined or have krb5/sssd/third-party-tools installed)
Virtual IP (keepalive) to access the Swarm
Traefik as docker proxy (wildcard pointing to Virtual IP)
NFSv4 to bind mount docker volumes
Active Directory

This how I did it:

Thx to @jovton I managed to use his hack to get SSSD up & running inside the container. I did not mount any files/dirs from the host, instead I create necessary directories during build and execute SSSD from "run_script" during boot.
Krb5-user is also configured to receive valid tickets.

Result:

[sssd[be[VICREM]]] [simple_bind_send] (0x0100): Executing simple bind as: CN=ldap-bind-user,OU=users,DC=vicrem,DC=se
[sssd[be[VICREM]]] [fo_set_port_status] (0x0100): Marking port 3269 of server 'ldap.vicrem.se' as 'working'
[sssd[be[VICREM]]] [set_server_common_status] (0x0100): Marking server 'ldap.vicrem.se' as 'working'
[sssd[be[VICREM]]] [sysdb_idmap_store_mapping] (0x0100): Adding new ID mapping [S-1-5-21-1483676591-1150964932-725778543][S-1-5-21-1482316591-1422945922-725876543][297]

My AD user can login using Windows Auth :)

Repo: https://github.com/vicrem/mssql.git

//Victor

[jovton]

@vicrem wow! Thanks Victor. I will have to give this a try.

[vicrem]

@jovton tell me if there something you need help with :)

[vicrem]

Authentication and authorization is now working

[vicrem]

I have a new setup where I create the keytab during build. Can someone return with feedback? Recommendation is to try it with a test AD.

[mrlioncub]

I would like to see the configuration of the SQL server for authentication from Kerberos (keytab file).

[vicrem]

@mrlioncub have a look in here -> https://github.com/vicrem/mssql.git

The only configuration I have for SQL is found in initialization.sh (line 148-155) the rest is made by krb and sssd

[jblesener]

MS also has some documentation on the process here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-containers-ad-auth-adutil-tutorial?view=sql-server-ver15

[hopoffbaby]

Hi All,

Myself and some of my colleagues have been trying to get AD auth for mssql working inside docker for about 3 days now. We seem frustratingly close but cannot quite get it.

Firstly, following the MS documentation to install MSSQL directly onto Linux and connect it to AD works fine in our environment. I followed these instructions:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver15

Our goal is to ultimately get this working in Kubernetes. Before complicating things with K8S though we tried to get it working in Docker. This is where the fun begins!

We can get it to connect to AD and we can add users. However the users must be added using the long domain name. For example BARF1.COM/user1. The problem is when using SSMS or sqlcmd -E you can only use the short name BARF1/user1. This then gives the error (bearing in mind this works find directly installed on a host):

2021-02-19 14:42:57.19 Logon       Error: 17806, Severity: 20, State: 14.
2021-02-19 14:42:57.19 Logon       SSPI handshake failed with error code 0x80090304, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The Local Security Authority cannot be contacted   [CLIENT: 172.26.63.14]
2021-02-19 14:42:57.22 Logon       Error: 18452, Severity: 14, State: 1.
2021-02-19 14:42:57.22 Logon       Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: 172.26.63.14]

The steps we followed to get to this point were:

# create mssql user manually

setspn -A MSSQLSvc/k8ssql1.barf1.com:1433 mssql
setspn -A MSSQLSvc/k8ssql1.barf1:1433 mssql
setspn -A MSSQLSvc/k8ssql1:1433 mssql

setspn -A MSSQLSvc/hpc-d-hpc-k8-n3.barf1.com:1433 mssql
setspn -A MSSQLSvc/hpc-d-hpc-k8-n3.barf1:1433 mssql
setspn -A MSSQLSvc/hpc-d-hpc-k8-n3:1433 mssql

kinit mssql@BARF1.COM
kvno mssql@BARF1.COM
kvno MSSQLSvc/k8ssql1.barf1.com:1433@BARF1.COM

ktpass /princ MSSQLSvc/k8ssql1.barf1.com:1433@BARF1 /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'
ktpass /princ MSSQLSvc/k8ssql1.barf1.com:1433@BARF1.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'

ktpass /princ MSSQLSvc/hpc-d-hpc-k8-n3.barf1.com:1433@BARF1 /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'
ktpass /princ MSSQLSvc/hpc-d-hpc-k8-n3.barf1.com:1433@BARF1.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'

ktpass /princ MSSQLSvc/k8ssql1:1433@BARF1 /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'
ktpass /princ MSSQLSvc/k8ssql1:1433@BARF1.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'

ktpass /princ MSSQLSvc/hpc-d-hpc-k8-n3:1433@BARF1 /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'
ktpass /princ MSSQLSvc/hpc-d-hpc-k8-n3:1433@BARF1.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'


ktpass /princ mssql@BARF1.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass PASSWORD'
ktpass /princ mssql@BARF1.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'

ktpass /princ mssql@BARF1 /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'
ktpass /princ mssql@BARF1 /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser BARF1\mssql /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass 'PASSWORD'

This resulted in a keytab as follows

[root@hpc-d-hpc-k8-n3 ~]# klist -k mssql.keytab Keytab name: FILE:mssql.keytabKVNO Principal
---- --------------------------------------------------------------------------
   2 MSSQLSvc/k8ssql1.barf1.com:1433@BARF1
   2 MSSQLSvc/k8ssql1.barf1.com:1433@BARF1.COM
   2 MSSQLSvc/hpc-d-hpc-k8-n3.barf1.com:1433@BARF1
   2 MSSQLSvc/hpc-d-hpc-k8-n3.barf1.com:1433@BARF1.COM
   2 MSSQLSvc/k8ssql1:1433@BARF1
   2 MSSQLSvc/k8ssql1:1433@BARF1.COM
   2 MSSQLSvc/hpc-d-hpc-k8-n3:1433@BARF1
   2 MSSQLSvc/hpc-d-hpc-k8-n3:1433@BARF1.COM
   2 mssql@BARF1.COM
   2 mssql@BARF1.COM
   2 mssql@BARF1
   2 mssql@BARF1

We then map this into the container @ /var/opt/mssql/secrets/mssql.keytab, along with the krb5.conf to /etc/krb5.conf and /var/opt/mssql/krb5.conf

The krb5.conf file looks as follows:

[logging]
default = FILE:/tmp/krb5.log

[libdefaults]
 default_realm = BARF1.COM
 clockskew = 5000
 rdns = false
 udp_preference_limit = 0
 forwardable = True
 dns_lookup_realm = True
 dns_canonicalize_hostname = True

[realms]
 BARF1.COM = {
    kdc =<ad server>
    admin_server = <ad server>
    default_domain = BARF1.COM
 }

[domain_realm]
 barf1.com = BARF1.COM
 .barf1.com = BARF1.COM
 barf1 = BARF1.COM
 .barf1 = BARF1.COM

and finally we have the mssql.conf file mapped to /var/opt/mssql/mssql.conf, which looks like:

[network]
privilegedadaccount = mssql
kerberoskeytabfile = /var/opt/mssql/secrets/mssql.keytab
disablesssd = true
enablekdcfromkrb5conf = true

It is so frustrating because

1. This works directly on a Linux host, but not in a container
2. I can add users but using BARF1.COM instead of BARF1
3. sqlcmd and SSMS forces the user to log in with BARF1

Any help would be GREATLY appreciated. We have looked far and deep over the interwebz and this thread is the most promising thing I have found.

I have tried @jovton suggestion of mapping in sssd content from the host machine with no success, and I have also tried the docker-compose from @vicrem but also no luck. I see errors about LDAP bind issues and failure to create keytabs. I also tried the manual create option too.

I'm about ready to give up on this and just call it impossible. The documentation from Microsoft, while on the surface looks detailed, just does not work for me at all! @vin-yu Any updates on the docs?

Fingers crossed one of the gurus here can help :)

Thanks,
hopoffbaby

[vicrem]

Hi @hopoffbaby, have a look on re_expression in SSSD, it might help you.

[takbgit]

Hi @hopoffbaby, we have been hitting the exact same issues as you and follow the exact same procedures.

We just got it to work by adding -u 0 to the podman container create command to force sql server to run as root.

So this looks more like a bug with running sql server as non-root.

I believe that SQL Server 2017 ran as root. Guessing that MS haven't quite got the rootless working right in 2019 yet.

[hopoffbaby]

@takbgit

I'm not sure about podman and rootless, but I did manage to get this solved with a support ticket to Microsoft.

The issue in the end for me was permissions on the mapped in krb5 and keytab files etc, and the most surprising was having to add COM to the search path in resolv.conf.

After that I was able to add the users with the short domain name and integrated Auth from ssms and sqlcmd worked as expected.

Cheers

[amvin87-zz]

There is a new documentation to configure AD Auth for containers and you can all do it from Linux machines using adutil, please follow the link documented here:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-containers-ad-auth-adutil-tutorial?view=sql-server-ver15

[hopoffbaby]

@amvin87 this is the guide I followed. It might be worth adding the info about adding COM into resolv.conf as this is what fixed the problem for me and was what was advised by Microsoft premier support

[amvin87-zz]

thank you for the feedback. I will edit this document or will work on creating a troubleshooting document that will have this documented and other steps to follow when configuring AD auth for SQL containers.

[DukaJ]

Hello everyone,

I can see that this topic is still active, I would like to ask.
My case is pretty simple:
I have .NET Core application which authenticate users through Windows AD. Everything worked smoothly until I wanted to dockerize my solution and place it inside Linux/Windows container.

My question is:
Is it possible to connect to Windows AD through Linux or Windows containers? If it is, how? (Is the only way using gMSA?)

I've tried many things but I feel like I'm in endless loop.

Thank you in advance!

[amvin87-zz]

Hi @DukaJ here the question was specific to SQL Server running on Linux containers to support AD auth, for a .net application running on container supporting AD auth, I am not aware of the answer.

[hopoffbaby]

@DukaJ

I'd like to know that too. Although not sure it's related to this topic. From what I understand you need to use gMSA. I have several iis website that need as Auth too

[DukaJ]

@amvin87, @hopoffbaby
Thank you guys, I'll look up somewhere else!

[akazakov]

yes, you can use AD enabled servers on docker / k8s without gMSA (including SQL server on linux). You just need to setup the keytabs and spns for your service correctly.

[amvin87-zz]

You can refer to this documentation https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-containers-ad-auth-adutil-tutorial?view=sql-server-ver15 and this video for a demo https://www.youtube.com/watch?v=A0mn9928N48 to configure AD auth for SQL on Linux containers.

[wsaeed]

Here is my scenario, need some help configuring AD authentication for SQL Server

Host Machine: Windows - Domain Joined
SQL Server: SQL 2019 image running on Ubuntu container

How do I enable AD authentication for SQL Server?

[akazakov]

@wsaeed you need a few things

1. Correct krb5.conf file inside the container. To verify that it is correct check that using kinit, you should be able to get tickets.
2. create a sql user in AD. Make a key tab and put it into container. Set principal name and path to the key tab in your mssql.conf. I recommend using “no sssd” option, because it is much simpler and has less moving parts. (See mssql Linux docs how to do that)
3. Create a SPN in AD associated with the sql user. You should be able to get a ticket for the mssqlsvc, you can check that using kvno tool. You will need a stable dns address, so either a load balancer or you always run it on the same host, because spn is pointing to a specific host name.

This is it.