Authenticate via gMSA Account through SSMS

Question

Authenticate via gMSA Account through SSMS

HA4g3n

Mr or Mrs. 500

Points: 537
More actions
February 22, 2018 at 6:05 am

#327580

Hello,
Im trying to Authenticate via an gMSA account that is based on computer membership of an AccessGroup in AD that works in other purposes.
Management Server 2016 GUI - Accessing from to --> SQL 2016 Core
How can i authenticate with the gMSA via SSMS, i have googled for connection parameters and the SSMS log book for info but im stuck.
I have added the MGM server and rebooted+ verified that gMSA account is installed and can be authenticated. Same gMSA is used for services on the Core server.
The SQL server is installed in mixed mode for SQL and AD authentication.
The errorlog says:
2018-02-22 14:09:16.40 Logon Error: 18456, Severity: 14, State: 58.
2018-02-22 14:09:16.40 Logon Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xxx.xxx.xxx.xxx]
Any tip is welcome.
Thanks
HA4g3n

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

HA4g3n - Thursday, February 22, 2018 6:05 AM
Hello,
Im trying to Authenticate via an gMSA account that is based on computer membership of an AccessGroup in AD that works in other purposes.
Management Server 2016 GUI - Accessing from to --> SQL 2016 Core
How can i authenticate with the gMSA via SSMS, i have googled for connection parameters and the SSMS log book for info but im stuck.
I have added the MGM server and rebooted+ verified that gMSA account is installed and can be authenticated. Same gMSA is used for services on the Core server.
The SQL server is installed in mixed mode for SQL and AD authentication.
The errorlog says:
2018-02-22 14:09:16.40 Logon Error: 18456, Severity: 14, State: 58.
2018-02-22 14:09:16.40 Logon Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xxx.xxx.xxx.xxx]
Any tip is welcome.
Thanks
HA4g3n

You can't use the managed service accounts to login to SQL Server.
The authentication type Active Directory - Password is used with Azure - for Azure Active Directory authentication. Not sure if you wanted to use that.

Sue

HA4g3n Mr or Mrs. 500 Points: 537 More actions · Answer 2

Sue_H - Thursday, February 22, 2018 6:50 AM
You can't use the managed service accounts to login to SQL Server.
The authentication type Active Directory - Password is used with Azure - for Azure Active Directory authentication. Not sure if you wanted to use that.
Sue

Hello Sue,

ok, so the access is given but canÂ´t be impersonated for connecting this way.
Applications can still use this authentication account i guess for accessing the SQL.

Thanks

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

HA4g3n - Thursday, February 22, 2018 7:07 AM
Sue_H - Thursday, February 22, 2018 6:50 AM
You can't use the managed service accounts to login to SQL Server.
The authentication type Active Directory - Password is used with Azure - for Azure Active Directory authentication. Not sure if you wanted to use that.
Sue
Hello Sue,
ok, so the access is given but canÂ´t be impersonated for connecting this way.
Applications can still use this authentication account i guess for accessing the SQL.
Thanks

I'm not a DBA, but my understanding is that Managed Service Accounts are for use by services (eg, the SQL Server service), not by other applications accessing SQL Server databases.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

Phil Parkin - Thursday, February 22, 2018 8:42 AM
I'm not a DBA, but my understanding is that Managed Service Accounts are for use by services (eg, the SQL Server service), not by other applications accessing SQL Server databases.

This is my understanding. These are accounts that are not used for interactive logins.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

Agreed, in case you need any more people to say it. These accounts are not meant to be used by humans. Use them to run your services.

HA4g3n Mr or Mrs. 500 Points: 537 More actions · Answer 6

Phil Parkin - Thursday, February 22, 2018 8:42 AM
I'm not a DBA, but my understanding is that Managed Service Accounts are for use by services (eg, the SQL Server service), not by other applications accessing SQL Server databases.

Hello and thanky you for answearing.

Yeah thatÂ´s my point of view aswell. But i have a application that uses IIS and the guide is posted that you can use gMSA to authenticate from the application server to the SQL.
Very odd situation but im gonna evaluate it and giving it a try.

Thanks

Alexander Zhang Default port Points: 1497 More actions · Answer 7

This is interesting. Basically, I don't think there is much essentially difference between services and other applications. In the other word, it's very possible. Here is a demo.

GASQL.com - Focus on Database and Cloud

Sue_H SSC Guru Points: 90860 More actions · Answer 8

Alexander Zhang - Thursday, February 22, 2018 11:01 AM
This is interesting. Basically, I don't think there is much essentially difference between services and other applications. In the other word, it's very possible. Here is a demo.

Is that with using PSExec ?

Sue

Alexander Zhang Default port Points: 1497 More actions · Answer 9

Sue_H - Thursday, February 22, 2018 11:12 AM
Alexander Zhang - Thursday, February 22, 2018 11:01 AM
This is interesting. Basically, I don't think there is much essentially difference between services and other applications. In the other word, it's very possible. Here is a demo.

Is that with using PSExec ?
Sue

I used some home-made application. And I think PSExec can do the same thing.

GASQL.com - Focus on Database and Cloud

Sue_H SSC Guru Points: 90860 More actions · Answer 10

Alexander Zhang - Thursday, February 22, 2018 11:14 AM
Sue_H - Thursday, February 22, 2018 11:12 AM
Alexander Zhang - Thursday, February 22, 2018 11:01 AM
This is interesting. Basically, I don't think there is much essentially difference between services and other applications. In the other word, it's very possible. Here is a demo.

Is that with using PSExec ?
Sue
I used some home-made application. And I think PSExec can do the same thing.

Yes PSExec can emulate local system, managed service accounts, etc. But I can't use those accounts as a direct login unless I run a command window as Administrator to launch PSExec to open a command window as one of those accounts and then launch SSMS in the Window opened up from PSExec. From there I can use Windows authentication as which ever account I used with PSExec.
So I'm not sure an app can use that in something like a connection string.

Sue

Alexander Zhang Default port Points: 1497 More actions · Answer 11

Sue_H - Thursday, February 22, 2018 11:35 AM
Alexander Zhang - Thursday, February 22, 2018 11:14 AM
Sue_H - Thursday, February 22, 2018 11:12 AM
Alexander Zhang - Thursday, February 22, 2018 11:01 AM
This is interesting. Basically, I don't think there is much essentially difference between services and other applications. In the other word, it's very possible. Here is a demo.

Is that with using PSExec ?
Sue
I used some home-made application. And I think PSExec can do the same thing.
Yes PSExec can emulate local system, managed service accounts, etc. But I can't use those accounts as a direct login unless I run a command window as Administrator to launch PSExec to open a command window as one of those accounts and then launch SSMS in the Window opened up from PSExec. From there I can use Windows authentication as which ever account I used with PSExec.
So I'm not sure an app can use that in something like a connection string.
Sue

Yeap, no 'direct' way. If it's really needed, something special has to be done in application. BTW, it's not so hard to implement the logic in application code. The only question is, I'm not sure if it's worth doing that:-)

GASQL.com - Focus on Database and Cloud

Sue_H SSC Guru Points: 90860 More actions · Answer 12

Alexander Zhang - Thursday, February 22, 2018 12:11 PM
Sue_H - Thursday, February 22, 2018 11:35 AM
Alexander Zhang - Thursday, February 22, 2018 11:14 AM
Sue_H - Thursday, February 22, 2018 11:12 AM
Alexander Zhang - Thursday, February 22, 2018 11:01 AM
This is interesting. Basically, I don't think there is much essentially difference between services and other applications. In the other word, it's very possible. Here is a demo.

Is that with using PSExec ?
Sue
I used some home-made application. And I think PSExec can do the same thing.
Yes PSExec can emulate local system, managed service accounts, etc. But I can't use those accounts as a direct login unless I run a command window as Administrator to launch PSExec to open a command window as one of those accounts and then launch SSMS in the Window opened up from PSExec. From there I can use Windows authentication as which ever account I used with PSExec.
So I'm not sure an app can use that in something like a connection string.
Sue
Yeap, no 'direct' way. If it's really needed, something special has to be done in application. BTW, it's not so hard to implement the logic in application code. The only question is, I'm not sure if it's worth doing that:-)

I know some of how PSExec and similar tools do it. But in addition the time to put something like that in an application it would still require admin access to initiate everything. And you don't want an application to have that type of access.

Sue

Alexander Zhang Default port Points: 1497 More actions · Answer 13

Sue_H - Thursday, February 22, 2018 12:34 PM
Alexander Zhang - Thursday, February 22, 2018 12:11 PM
Sue_H - Thursday, February 22, 2018 11:35 AM
Alexander Zhang - Thursday, February 22, 2018 11:14 AM
Sue_H - Thursday, February 22, 2018 11:12 AM
Alexander Zhang - Thursday, February 22, 2018 11:01 AM
This is interesting. Basically, I don't think there is much essentially difference between services and other applications. In the other word, it's very possible. Here is a demo.

Is that with using PSExec ?
Sue
I used some home-made application. And I think PSExec can do the same thing.
Yes PSExec can emulate local system, managed service accounts, etc. But I can't use those accounts as a direct login unless I run a command window as Administrator to launch PSExec to open a command window as one of those accounts and then launch SSMS in the Window opened up from PSExec. From there I can use Windows authentication as which ever account I used with PSExec.
So I'm not sure an app can use that in something like a connection string.
Sue
Yeap, no 'direct' way. If it's really needed, something special has to be done in application. BTW, it's not so hard to implement the logic in application code. The only question is, I'm not sure if it's worth doing that:-)
I know some of how PSExec and similar tools do it. But in addition the time to put something like that in an application it would still require admin access to initiate everything. And you don't want an application to have that type of access.
Sue

Partially agree. To some degree, I think it depends on need, design and implements. An acceptable solution might be,
1. wrap this special things in seperated component
2. this component runs under admin or other privilege as needed
3. this component provides only specific required interfaces.
It looks just like a proxy somehow:-)

And again, I'm not sure if it's worth doing that.

GASQL.com - Focus on Database and Cloud