Add support for Kerberos/Active Directory/"windows" authentication
EdiDD opened this issue · 102 comments
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.
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.
Even with workaround like linking to openldap container or something like that ?
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.
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
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.
This was opened 7 months ago. Any news or progress?
We are starting to work on this in the next couple of weeks. We will update as we have information or bits to share.
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.
It may be useful to clarify that there are two, fundamentally-different issues at play here.
- 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.
- 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.
So, no, it's not possible yet?
Still working on it...
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?
@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 ?
Not yet. Working on it...
Is there any timescale to supporting this? Struggling with 3rd party apps using hardcoded Integrated Security in connection DB connection string.
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.
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.
is there any workaround to use SASS with container ?
@twright-msft is there any documentation on how to set this up for the 2019 containers?
@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:
- 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).
- 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.
@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.
@esauser - Ah, understood. It's not ready for testing just yet. We'll come back around and update this issue when it is ready.
@twright-msft Can you offer any kind of timeline? Are we talking days, weeks, month, months, etc.?
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.
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.
@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.
@esauser - estimate: small number of months for SQL 2019 on Linux containers
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].
-
Type exit and relaunch docker
docker run -it -p 49176:49176 mssql:latest -
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.
With current auth requirements that does not seem to be reliable approach. What about Win2019 IIS container to authenticate against SQL?
estimate: small number of months for SQL 2019 on Linux containers
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.
Any update about AD support on Docker containers on Linux? Is there any other way of creating Windows users on SQL Server?
@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!
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...
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?
@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.
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.
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
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!
Any update for AD authentication for linux docker image in SQL Server 2019
Please update?!?!?!
@twright-msft: - estimate: small number of months for SQL 2019 on Linux containers
Interested in this as well for 2019 Linux containers. Would be very useful in our organization.
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.
Any idea when Kerberos will be available on Containers? looks like a Major roadblock in moving DB to containers
Hey,
it is 2020(!) - any news with the kerberos implementation?
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.
@lubo. Sounds great. What'd be more terrific if this was supported by Microsoft as promised years ago...
@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.
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 🤓.
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.
@jovton Exactly, I've no idea why. Would be great if something from Microsoft explained why.
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.
OMG! I think it works! The mapping of the volumes made a difference to me. Before that I couldn't do this:
CREATE LOGIN [DOMAIN\user] FROM WINDOWS;
It would tell me...
Windows NT user or group "DOMAIN\user" not found. Check the name again
But now, it works. Hmmm.
@lubo i think it need to be joined to the domain in order to create the service principles with setspn and ktpass and so on.
UPDATE: I still have domain trust issues though:
@jovton Switch to
Azure Active Directory - Integrated
authentication, and tell the client to accept the certificate.
@turboaaa what is the output of cat /var/opt/mssql/log/errorlog, assuming that is the configured logs location?
@jovton I don't have this configured in my environment at the moment, ended up just using a Red Hat VM for this. Working on other projects.
During the install on Red Hat I did run into some keytab issues, and had to make sure the kvno numbers matched up with the service account I was using. I have not gone back to the containers to apply the lessons learned.
@jovton What is a SPN and why should you care? makes me believe you don't need a computer account for that. Yes, in the example, the server is connected to a domain, but all the magic AFAIK happens on the domain account and nothing directly indicates that it has to be a computer account. But maybe I'm getting it wrong.
@turboaaa thanks for the hint. but i'm afraid Azure Active Directory - Integrated doesn't work either.
it could be that something is not right with either my AD domain controller setup and/or DNS (win server 2019 running in vm), or a problem in my krb5 / sssd conf files, or the self-signed certificate used in my mssql.conf created via openssl, or a combination of all those things. right now it's hard to tell and will take some deeper longer hours of investigation.
Re: SPN, you may be right. i have very little knowledge about these things and I'm still learning.
Under options, check the "Trust server certificate" under Connection Properties.
Under options, check the "Trust server certificate" under Connection Properties.
Thanks. I tried that, but still no luck I'm afraid. Same error (both Windows auth and Azure AD).
@jovton
Went back over my notes, I never played around with certificates on my installation. Which is why I told my data analyst to force trusting it (not good practice, but the risk in this environment is very low.) I just wanted mssql to run on something that didn't need rebooting every week.
Can you build clean without importing a certificate?
This is from /var/opt/mssql/log/errorlog:
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: 192.168.200.1]
The certificate I was talking about is the one configured with the tlscert and tlskey variables in the mssql.conf file, so I think this is not related to the domain trust issues, and I'm not using connection encryption. I also haven't specified or used any certificates in the krb5 or sssd conf files. I used "realm" to join the domain and it said it was successful.
I also tried to "sync" the krb5.keytab and mssql.keytab files using ktutil, rkt / wkt, so they have the same entries. It doesn't appear to have helped.
I'm pretty new to docker and docker builds, I may not know what I'm doing so please bare with me 😸.
Did you use SPN or MSA? I had problems with SPN and ended up using MSA.
https://michaelwiki.geekgalaxy.com/index.php/MSSQL_RHEL_7.7
Did you use SPN or MSA? I had problems with SPN and ended up using MSA.
https://michaelwiki.geekgalaxy.com/index.php/MSSQL_RHEL_7.7
I use SPN. I haven't tried MSA yet, not sure how to proceed with that. I'll try the steps in the article/post. :)
It is very likely an SPN / keytab file issue, I will try to re-create them and double check the access to the keytab file for the container user. For now I'll wait to see if anyone else here has more luck before I comment any more, or until I myself have a breakthrough.
OKAY, some success!!!
i tried doing all things except not in a docker container, but via the stand-alone mssql-server installation, and then Windows/AD integrated authentication works beautifully. The problem is the date/time. When simply running the default SQL Server container image provided by Microsoft, the date command in the container reports UTC, while the host (joined to the AD domain) is on a different timezone, which will cause the untrusted domain issues. Now, to figure out if there some way to permanently set the correct date / time / timezone in the container so it it always the same as the host's...
Does this not work?
-e TZ=America/New_York
Or mounting the local timezone file into the container?
Does this not work?
-e TZ=America/New_York
@turboaaa I tried that, but doesn't appear to work. Trying to figure out if the tzdata package is installed in the container, and if not how to do that.
it appears MS has updated their images to account for the tzdata package dependency, so that is sorted.
but what i did wrong was that i wanted to read the host timezone into a variable and use it in the subsequent docker run command, but i used single quotes ( ' ) instead of double ( " ) to enclose the environment variable parameter values (for -e), so it did not pass the correct timezone into the container. the following was my setup and it works perfectly...
export TZ=$(cat /etc/timezone); docker run \
-e "TZ=$TZ" \
-e "SET_CONTAINER_TIMEZONE=true" \
-e "CONTAINER_TIMEZONE=$TZ" \
-e "ACCEPT_EULA=Y" \
-e "MSSQL_SA_PASSWORD=mysupersecretpassword" \
-e "MSSQL_PID=Developer" \
-p 1433:1433 \
-v /var/opt/mssql:/var/opt/mssql \
-v /var/lib/sss/pipes/:/var/lib/sss/pipes/:rw \
-v /etc/sssd/:/etc/sssd/:ro \
-v /etc/krb5.conf:/etc/krb5.conf:ro \
-v /etc/ipa/ca.crt:/etc/ipa/ca.crt:ro \
-v /etc/nsswitch.conf:/etc/nsswitch.conf:ro \
-v /etc/pam.d/:/etc/pam.d/:ro \
-v /etc/timezone:/etc/timezone:ro \
-v /etc/localtime:/etc/localtime:ro \
-h ubuntu.mannetjie.net \
--user mssql --name "sql1" --restart unless-stopped \
-d mcr.microsoft.com/mssql/server:2019-latest
this assumes of course that the AD join was a success, keytab file setup was correct and mssql.conf reflects these things.
@jovton Do I understand correctly you've successfully set up kerberos on a docker based SQL server? I'm interested in how exactly it's done because I've to do the same. If you can share more details I would appreciate.
@jovton Do I understand correctly you've successfully set up kerberos on a docker based SQL server? I'm interested in how exactly it's done because I've to do the same. If you can share more details I would appreciate.
He mounted additional resources from the host that are generally locked down. Please note this method does not work on most kubernetes clusters, or container hosts you do not control.
A fully contained solution has yet to be created. I have been able to move a number of items into the container, i.e. Pam and kerberose cache, but some of the other resources are kernel locked.
@gaborgsomogyi turboaaa is right. My setup worked because the container uses some of the host's resources (krb5.conf, sssd pipes/sockets etc.) by attaching data volumes: Mount a host directory as data volume. In my case is was the (docker) host that was already "joined" to the AD domain, and not the container. In certain scenarios like a fully contained environment it may not be possible for the host to share locked-down resources with the containers, such as those mentioned by turboaaa, i.e. kubernetes cluster. this is the next challenge and milestone :)
UPDATE: After seeing this https://docs.pagure.org/SSSD.sssd/design_pages/sockets_for_domains.html it's all beginning to make sense now, It seems that the domains you can authenticate against entirely depends on how the host is set up. Looks like a work in progress.
@jovton After couple of days struggle I can't really make a successful server setup without docker and linux :) I'm using pure widows on AWS but no success to see that SQL server would use kerberos: https://gist.github.com/gaborgsomogyi/e27c761e05d451ed794591e38a903402
Actually auth_scheme is always SQL :/
@jovton After couple of days struggle I can't really make a successful server setup without docker and linux :) I'm using pure widows on AWS but no success to see that SQL server would use kerberos: https://gist.github.com/gaborgsomogyi/e27c761e05d451ed794591e38a903402
Actuallyauth_schemeis alwaysSQL:/
Hi @gaborgsomogyi. Unfortunately I'm not familiar with how things work on AWS, and I have only tried AD auth on Ubuntu 18.04 LTS in a Hyper-V VM on a local NAT network. I'm am busy writing up some posts on my experiences.
In the mean time, I spotted this bit of information here: Use Active Directory authentication with SQL Server on Linux
Well, well... was this info there before and I just missed it? Does that mean SQL Server can completely bypass the SSSD mechanisms and we don't have to mount-bind to /var/lib/sss/pipes in our containers? Perhaps you're right @lubo, all this "AD joining" might be for naught 😁...
@jovton LDAP auth would solve almost all issues! Kerberose is really only needed for seamless SSO.
Great find.
There are a few of us at my workplace bumping into this issue. Would be great to have a solution.
@twright-msft Hi Travis. whats the current situation with resolving this now.
Could we possibly have a recap, and summary. ;-)
much appreciated, as we're going to be using Docker SQL Linux 2019 for CICD.
Advanced Many Thanks Travis for all the hard work people put into this. respect!
We're working on some tooling for AD for SQL Server on Linux containers which will simplify this experience. However the first version will require that the container host is able to join the AD domain/realm-joined.
is there any feasable way to by-pass the joining to AD and still have windows authentication working on DDL Statements? for example:
CREATE USER [domain\ntaccount] WITH DEFAULT_SCHEMA = [domain\ntaccount];
According to what i understand, IPs could be different in containers, and each dev would be spinning up/down their containers, so i believe the pain of joining to AD would not suffice in a container world. Or; am i missing something?
could you give us a rough ballpark timeframe of when the second version (no join required) will be available?
is there any feasable way to by-pass the joining to AD and still have windows authentication working on DDL Statements?
Keep in mind that they are using kerberos for AD authentication. Kerberos is protected by the Linux kernel, which containers should not have direct access to. Would love to see an LDAP option as there are use cases where seamless sign in is not a necessity.
According to what i understand, IPs could be different in containers, and each dev would be spinning up/down their containers, so i believe the pain of joining to AD would not suffice in a container world. Or; am i missing something?
It's not the containers that join the domain, it's the host for the containers. To AD the kerberos traffic is coming from the host and not the container. If you are running a kubernetes cluster on VMs you control, this isn't a logistical problem (though binding to protected host resources opens up security implications). If you wanted to deploy this on serverless cloud resources, you wouldn't be able to join the host to the domain.
Since BDC on Openshift now supports Windows Auth, has there been any progress on this?
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
Authentication and authorization is now working
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.
I would like to see the configuration of the SQL server for authentication from Kerberos (keytab file).
@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
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
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:
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
- This works directly on a Linux host, but not in a container
- I can add users but using BARF1.COM instead of BARF1
- 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
Hi @hopoffbaby, have a look on re_expression in SSSD, it might help you.
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.
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
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
@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
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.
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!
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.
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
@amvin87, @hopoffbaby
Thank you guys, I'll look up somewhere else!
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.
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.