Martin Andel
asked on
Cannot connect to SQL Server as a member of AD group
I have migrated a test database to a domain joined Ubuntu 18.04 server with MS SQL Server Studio instance. The AD domain security group authentication has been set up on the database but when I try connecting as a group user, the CRM client software gives the following error:
"Login failed. The login is from an untrusted domain and cannot be used with integrated authentication."
I was told to add "Integrated Security = true" to the client connection string though because before this I was getting:
"Login failed for user ''.
at System.Data.SqlClient.SqlI
"Login failed. The login is from an untrusted domain and cannot be used with integrated authentication."
I was told to add "Integrated Security = true" to the client connection string though because before this I was getting:
"Login failed for user ''.
at System.Data.SqlClient.SqlI
ASKER
"On Ubuntu does your AD definition includes addomain and addomain.suffix?"
Where or how do I find that out?
"What errors are reported on the Ubuntu SQL side?"
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
and
SSPI handshake failed with error code 0x80090308, 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 token supplied to the function is invalid
Where or how do I find that out?
"What errors are reported on the Ubuntu SQL side?"
Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
and
SSPI handshake failed with error code 0x80090308, 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 token supplied to the function is invalid
See the following link' https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication?view=sql-server-ver15
Look at krb.conf
Status of trusted connection=yes in addition to integrated security.
In short, the token exchange is not trusted.
Are you able to use ssms client on a Windows system to connect to the SQL server using Windows login?
Look at krb.conf
Status of trusted connection=yes in addition to integrated security.
In short, the token exchange is not trusted.
Are you able to use ssms client on a Windows system to connect to the SQL server using Windows login?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I went through the whole article, setting up SPNs and associating them to the MSA to no avail. Still getting the same error.
Have not setup SQL 2017 in a Linux environment.
One thing to check us under which credentials iis the sqlserver running?
Is it running under an AD service account?
One thing to check us under which credentials iis the sqlserver running?
Is it running under an AD service account?
I have both individual login and group login through windows AD authentication working properly
from Windows to Linux, Linux to Linux, and Linux to Windows.
To isolate the problem, I'd suggest you logon to your Linux box with a domain user account and start from there
using SQLCMD to troubleshoot.
from Windows to Linux, Linux to Linux, and Linux to Windows.
To isolate the problem, I'd suggest you logon to your Linux box with a domain user account and start from there
using SQLCMD to troubleshoot.
ASKER
One thing to check us under which credentials iis the sqlserver running?
mssql 1113 0.0 0.1 159916 23684 ? Ssl Nov05 0:00 /opt/mssql/bin/sqlservr
check is mssql a local /etc/passwd user
or wbinfo -u | grep mssql?
mssql@addomain as the login credential an option?
or wbinfo -u | grep mssql?
mssql@addomain as the login credential an option?
ASKER
I am a bit unclear about the binding of the service account that runs the SQL server on the linux box to the domain account created at the begining of the tutorial + the MSA required to complete the SPN keytab entries (option 2).
Are you talking about the part from Waynezhu's provided link:
kinit user@CONTOSO.COM
kvno MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@CONTOSO.COM
where contoso.com is replaces with your full AD name?
if that is what you've done,
add the same parameters for the AD without the suffix..
for one reason or another it reports a mismatch contoso.com from the connection going to contoso on the server or vice versa and something kicks it out.
not having personally installed this setup thus not ran into a similar situation. going a step a time to narrow where the conflict is.
i..e mssql@contoso is the SPN for the server.
In prior use of samba/ad joined the realm could be defined as contoso.com pointing to the existing AD/dc and contoso pointing to the same....
not sure where the separation exists on your side where it sees the same AD as two distinct ones.
kinit user@CONTOSO.COM
kvno MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@CONTOSO.COM
where contoso.com is replaces with your full AD name?
if that is what you've done,
add the same parameters for the AD without the suffix..
for one reason or another it reports a mismatch contoso.com from the connection going to contoso on the server or vice versa and something kicks it out.
not having personally installed this setup thus not ran into a similar situation. going a step a time to narrow where the conflict is.
i..e mssql@contoso is the SPN for the server.
In prior use of samba/ad joined the realm could be defined as contoso.com pointing to the existing AD/dc and contoso pointing to the same....
not sure where the separation exists on your side where it sees the same AD as two distinct ones.
ASKER
Are you talking about the part from Waynezhu's provided link:
Yeah, I am thinking that the problem could also lie in fact that I have created the AD user account as ubntusql01@mydomain.com as opposed to:
New-ADUser mssql -AccountPassword (Read-Host -AsSecureString "Enter Password") -PasswordNeverExpires $true -Enabled $true
So afterwards the Windows SPN was set as:
setspn -A MSSQLSvc/**SQLSRV.MYDOMAIN
Should I have sticked to the "mssql" account name as suggested?
Because as I did say earlier, on the ubuntu server the SQL service is running under the account named mssql.
you could , you could tell sql to start using ubntusql01 instead of mssql.
The issue is that you mixed and matched different things.
since you are running as mssql,
the quickest option is to run
setspn -A MSSQLSvc/**SQLSRV.MYDOMAIN
presumably this is not yet a production system.
The powershell you effectively created a new AD user in addition to the user you created within the aduc interface.
The issue is that you mixed and matched different things.
since you are running as mssql,
the quickest option is to run
setspn -A MSSQLSvc/**SQLSRV.MYDOMAIN
presumably this is not yet a production system.
The powershell you effectively created a new AD user in addition to the user you created within the aduc interface.
ASKER
But isn't an SPN just an account dependent label. I'e, if there isn't an account in my AD called msssql, what will that commmand put the label to?
double check whether you do or do not have an mssql account.
Check whether the mssql account under which you are running on the linux box exists in the /etc/passwd file or whether it actually exists
mssql has to exist if it is running.
either local /etc/passwd or in the AD
It is difficult for me to untangle which is which.
Check whether the mssql account under which you are running on the linux box exists in the /etc/passwd file or whether it actually exists
mssql has to exist if it is running.
either local /etc/passwd or in the AD
It is difficult for me to untangle which is which.
Microsoft online tutorial is sort of confusing. You should leave mssql (local user account) alone.
The service account (domain user account) is what I used for setspn.
The service account (domain user account) is what I used for setspn.
ASKER
double check whether you do or do not have an mssql account.
Although the ps -aux command shows the SQL server process running under mssql account, I cannot see the same account being listed in the /etc/passwd
You should leave mssql (local user account) alone.
You mean the local (existent/nonexistent?) Linux account I assume?
The service account (domain user account) is what I used for setspn.
Did you create one for this in AD? Can you please describe step-by-step what you did?
mssql is the local user created by my sql server installer during the installation.
The service account is an AD account created by your AD administrator.
On Linux, the command "id <username>" will be show user property.
Here are what I have on my server
$ id mssql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
$ id ssmsa
uid=964801109(ssmsa) gid=964800513(domain users) groups=964800513(domain users)
You can see the difference if it is a local or domain user.
I do not have the steps I documented. It has been a while back. One thing for sure, many
tries and errors were at then time. Also I made comments to the MS online document about
confusions, and they admitted and made certain corrections later.
The service account is an AD account created by your AD administrator.
On Linux, the command "id <username>" will be show user property.
Here are what I have on my server
$ id mssql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
$ id ssmsa
uid=964801109(ssmsa) gid=964800513(domain users) groups=964800513(domain users)
You can see the difference if it is a local or domain user.
I do not have the steps I documented. It has been a while back. One thing for sure, many
tries and errors were at then time. Also I made comments to the MS online document about
confusions, and they admitted and made certain corrections later.
ASKER
Here are what I have on my server
Yeah, I have a pretty similar thing:
id mssql
uid=999(mssql) gid=999(mssql) groups=999(mssql)
id ubntusql01
uid=1562404223(ubntusql01)
Does it your “setspn -L <serviceaccount>” output look right to you?
ASKER
Registered ServicePrincipalNames for CN=ubntusql01,CN=Users,DC=
SQLSRV/ubntusql01
HOST/ubntusql01
MSSQLSvc/**SQLSRV.MYDOMAIN
SQLSRV/ubntusql01
HOST/ubntusql01
MSSQLSvc/**SQLSRV.MYDOMAIN
Why those "**" surround the host name and port number?
ASKER
(facepalm) they are in the tutorial, I was wondering what they meant!
They don't have to be there I take it?
I wish Microsoft have added the command examples into that article as they do with PS tutorials...
They don't have to be there I take it?
I wish Microsoft have added the command examples into that article as they do with PS tutorials...
ASKER
It works now. Thanks guys!
ASKER
Thank you guys. I wouldn't be able to do this without you!
It is a pleasure. Thank you Martin for your kindness.
Thanks to Arnold as well.
Thanks to Arnold as well.
ASKER
Yes, of course. Thanks Arnold!
On Ubuntu does your AD definition includes addomain and addomain.suffix?
What errors are reported on the Ubuntu SQL side?