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.

[esauser]

@twright-msft Can you offer any kind of timeline? Are we talking days, weeks, month, months, etc.?

[bkraul]

How is it that Microsoft is already handling paid licensing of MSSQL for Linux (bare and container) but still has incomplete features?

This blows my mind. Twice this year we have had to go back on our plans to go to Linux because, even though Microsoft marketing information says SQL server is linux-ready, it is really not. This is a bit frustrating.

First, we could not move because AD was not supported. AD is now "sorta" supported, but Maintenance Plans are still not supported, neither is Reporting Services...I understand that this takes time and that the team is doing what it can, but maybe this should be made abundantly clear in places like here

This information matters to people in control of purchasing decisions, knowing this can make people like us in the IT team not look like idiots when we tell our company we are ready for Linux deployment.

[vin-yu]

There are users who do not need 100% parity between Windows and Linux to get started with their deployments with Linux.

We maintain a list of supported features on SQL Server on Linux documents here: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2017?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes?view=sql-server-2017

@esauser - no specific timeline, but I am hoping we can share something within a few months. We are currently experimenting on how we can set up active directory without joining the Container/Host Machine to the domain. I'll share as we progress.

[bkraul]

@vin-yu Understood on the parity situation, from a development standpoint. However, production approach does surely not follow this trend. And that still does not target the presented matter. Why would marketing not clearly present this in their SQL for Linux splash in the link I sent? Why do we have to dig through whitepapers in order to find out what the deal is? A simple:

• Not all features currently present on SQL Server for Linux. Click here for details.

Would suffice.

[twright-msft]

@esauser - estimate: small number of months for SQL 2019 on Linux containers

[NZUser01]

Found a workaround to allow users to authenticate via Active Directory for a Windows based docker image of SQL Server -

==================================================

You can make Windows Authentication work by creating a local user account with the same username and password as your domain account. Then grant the account access within SQL Server Management Studio (SSMS).

==================================================

Build a dockerfile that runs SQL server setup and installs the required DB features.

In the dockerfile add the following line to add your local account with the same username as your ad user account (before running SETUP.exe) RUN PowerShell.exe -Command New-LocalUser -Name "Your Ad Username" -Password (ConvertTo-SecureString -String "Your AD password" -AsPlainText -Force)

Then run the SETUP.exe of SQL Server with the following parameter in the dockerfile. /SQLSYSADMINACCOUNTS="Ad User Name"

=================================================

Next, find the port SQL Server runs on

Run your sql image

docker run -it mssql:latest

Identify SQL port it is running on by running

PowerShell.exe Get-EventLog -LogName Application | Where-Object {$_.EventID -eq 26022} | Select-Object * |more

Look for - Server is listening on [ 'any' "ipv6" 49176].

3. Type exit and relaunch docker

   docker run -it -p 49176:49176 mssql:latest

4. Open SSMS as the your ad domain user (shift right click on SSMS and go to run as different user).

   Connect to the ip address and port (identified above) of the docker host. The windows authentication box with your ad username should appear. Click on Connect.

   Presto! you are logged in as an administrator

To add additional ad users - run the following commands

docker run -it -p 49176:49176 mssql:latest PowerShell.exe $pwd = ConvertTo-SecureString -AsPlainText -String "Ad password" -Force New-LocalUser -Name "Ad Username" -Password $pwd

hostname (Note the hostname down - it will be the container name)

Go back to SSMS and in a query window run CREATE LOGIN ["hostname" \ "Ad Username"] FROM WINDOWS

Create your db user and grant your db permissions as you would usally do..

Open SSMS as that user like previously described and presto you can log in with your domain account.

The only gotcha is that you need to keep your active directory domain password in sync with the local user account.

[virtualas10]

With current auth requirements that does not seem to be reliable approach. What about Win2019 IIS container to authenticate against SQL?

[esauser]

@twright-msft

estimate: small number of months for SQL 2019 on Linux containers

@vin-yu

no specific timeline, but I am hoping we can share something within a few months.

Any update here? It's been both a small number of months and a few months.

[igcalvo]

Any update about AD support on Docker containers on Linux? Is there any other way of creating Windows users on SQL Server?

[bkraul]

@igcalvo "Small number of months...". I actually tried that at my workplace when asked when I'd be done with my project. It actually does work!

[turboaaa]

Looks like waiting is the best thing. I tried to follow the MSSQL Linux AD auth instructions, but in the container. It fails with a segmentation fault when adding the domain user, if only mssql supported PAM...

[imphasing]

For my team, AD authentication for windows containers is a big deal. This means we can maintain our existing security boundaries as well as use containers. This would allow us to do containerization easier - the exact same authn/authz system we have can be used, so hosting code on containers has very little barrier. We can just start doing it without worrying that we could be crossing security boundaries for our service accounts.

I experienced an issue related to this recently: running local development environment SQL server without being able to use integrated windows auth means I end up with tons of worthless credentials only used for development.. if I can just do windows auth there is no credential anywhere on my filesystem.. integrated auth for SQL containers for local development is like dream tier for me right now.

Future looking, I mostly want to use existing service domain accounts and maintain my existing security boundaries.. if I can join a container to my domain my life gets hella easier.

Can we get an update for a timeline on this?

[bkraul]

@vin-yu would love to tell you that you are part of the unseen few...That most shops don't need this feature and are lining up to purchase SQL server for linux to be used in docker containers with SQL-only authentication and missing features. I disagree. Apparently you do too. I know my comment is negative, but please understand that there is quite a bit of frustration. I am glad it is shared.

[turboaaa]

if I can join a container to my domain my life gets hella easier.

Just a quick clarification, generally you do not want to join your containers to a domain. Containers are made to be disposable which can make things messy, and has caused a conflict with how I want to use them as well.

I am not always building an application stack that needs simple access to a DB backend, but an entire data lake will be held here with multiple security groups and service accounts already created in AD. The business analysts I support are used to MSSQL and can not switch to MySQL at this time. I do not want to manage Windows servers, and moving this to a container that floats across a couple AWS instances makes my life a lot easier.

For all other containers I join the hosts to the domain, then share the local kerberos config with the container. Kerberos is only concerned with a couple hosts while the containers get destroyed and rebuilt as needed. But I agree that AD auth is a critical feature. At the very least open it up to PAM so we can use existing modules.

I have one more thing I want to try to get the auth to work without mssql crashing. I read others had issues with the mssql-tools causing segmentation faults, so tomorrow I will attempt to create the windows user using ssms instead. I have the ticket, I can initialize it with the spn, and I see cached credentials within the container.

[turboaaa]

Segmentation fault, just going to use AWS hosted MSSQL at this point. We have a working PoC for migrating to MySQL, maybe next year lol

[mattiaperi]

Any update about AD support on Docker containers on Linux? Is there any other way of creating Windows users on SQL Server?

@twright-msft: about the "AD support on Docker containers on Linux" topic, is there any news? It would be very much appreciated, thanks!

[anupinder]

Any update for AD authentication for linux docker image in SQL Server 2019

[666-cookie]

Please update?!?!?!

@twright-msft: - estimate: small number of months for SQL 2019 on Linux containers

[billabongrob]

Interested in this as well for 2019 Linux containers. Would be very useful in our organization.

[CharterSteel]

I too am interested in this. I was very enthusiastic about moving to sql on linux containers for the potential to do CUs quickly, but this is a roadblock.

[sachinyadav354]

Any idea when Kerberos will be available on Containers? looks like a Major roadblock in moving DB to containers

[kurdy91x]

Hey, it is 2020(!) - any news with the kerberos implementation?

[lubo]

I've been interested in deploying SQL Server as a Linux container with AD authentication and so I've been studying Active Directory authentication for SQL Server on Linux. Although the documentation explicitly says somewhere that the host (or the container in this case) has to be joined to domain, I don't think that's true. At least, I haven't found an argument why it should be true. From Tutorial: Use Active Directory authentication with SQL Server on Linux it looks like all you need is a correct /etc/krb5.conf, a correct keytab file, to set network.disablesssd to true and perhaps to set network.enablekdcfromkrb5conf to true. Unfortunately, I haven't had the time yet to test this out, but if I'm correct, I don't really see a reason why this shouldn't be supported. I haven't seen anybody pursuing this approach in this thread and I'm unsure whether it's because nobody has tried and shared the results or just because this wouldn't work. I base this theory on the fact that for a long time AD has been used in the same way with applications on platforms which can't be/aren't joined to AD and there're numerous articles on the internet describing how to do this with many popular applications and frameworks. For example, see All you need to know about Keytab files which says the same thing.

It'd be terrific if somebody else tried this out and shared the results or point out where I'm wrong.

[bkraul]

@lubo. Sounds great. What'd be more terrific if this was supported by Microsoft as promised years ago...

[lubo]

@bkraul What I mean is that according to "Option 2: Use third-party openldap provider utilities" in "Join to the AD domain" in "Join SQL Server on a Linux host to an Active Directory domain", this has already been supported. I think this paragraph supports my theory, that joining the host to domain isn't necessary at all:

SQL Server does not use third-party integrator's code or library for any AD-related queries. SQL Server always queries AD using openldap library calls directly in this setup. The third-party integrators are only used to join the Linux host to AD domain, and SQL Server does not have any direct communication with these utilities.

If the server can perform LDAP queries and handle authentication using Kerberos, what does it need the host to be joined to domain for? Does it actually need that? Isn't that just a mischaracterization of the requirements?

@twright-msft I'd very much appreciate if you commented on this.

[jovton]

I think this paragraph supports my theory, that joining the host to domain isn't necessary at all:

SQL Server does not use third-party integrator's code or library for any AD-related queries. SQL Server always queries AD using openldap library calls directly in this setup. The third-party integrators are only used to join the Linux host to AD domain, and SQL Server does not have any direct communication with these utilities.

@lubo Forgive me, but why does the article then go through all that trouble of joining the Linux installation to the domain first? I'm confused 😕. But perhaps you've got a point.

@turboaaa 's theory also sounds promising... may something similar to this can work?

docker run \
 --volume=/var/lib/sss/pipes/:/var/lib/sss/pipes/:rw  \
 --volume=/etc/sssd/:/etc/sssd/:ro  \
 --volume=/etc/krb5.conf:/etc/krb5.conf:ro \
 --volume=/etc/ipa/ca.crt:/etc/ipa/ca.crt:ro  \
 --volume=/etc/nsswitch.conf:/etc/nsswitch.conf:ro \
 --volume=/etc/pam.d/:/etc/pam.d/:ro \
 -h myubuntuhost.my.ad.domain ... etc, etc.

I got that from an Arctiq blog post: Are your Containers having an "Identity" Crisis?

I'll play with it a little and let you know 🤓.

[turboaaa]

Sadly I don't think the problem is with kerberose, but instead instability and inflexibility with mssql itself. Whenever something is not as expected mssql crashes with segmentation faults.

If you look at the docker file it is very basic. In fact, you should be able to follow the steps outlined in Microsoft's official documentation to enable kerberose authentication inside the container without binding to the host's keyfile (that was something I did out of ignorance, but it does work in some circumstances.) As long as you change the keyfile cache directory to someplace not controlled by the kernel (i.e. /tmp inside the container) you should be good to go.

I now think there is something else mssql is looking for and can not find in the container, and when it can't find it it crashes. At this point an strace would need to be performed to determine what is missing. I look forward to someone more knowledgeable than I to let us know what is going on.

[lubo]

@jovton Exactly, I've no idea why. Would be great if something from Microsoft explained why.