SSMS and Active Directory Authentication doesn't work with Azure SQL and non-Azure SQL
up vote
0
down vote
favorite
So we have Azure AD synced with our on-premise domain. We have an Azure SQL Server configured with the active directory admin. We also have a non Azure SQL server running on a virtual machine in Azure that is domain joined to this same domain. The following are the results of using the various authentication mechanisms offered by SQL Management Studio (SSMS). Can someone explain why the failures occur with the various options that should be supported?
Facts about environment:
- Passthrough authentication is the sign-in method configured on AD Connect
- Password hash sync is also enabled so password hashes are stored in Azure AD
- Azure SQL is configured with Active Directory admin
- Latest version of SSMS was downloaded when performing these test
- On premise account was used to test all scenarios
Domain joined client connecting to Azure SQL from SSMS
- Active Directory Password (PASS)
- Active Directory Universal (PASS)
- Windows Integrated (FAIL - not supported by Azure SQL)
- Active Directory Integrated (FAIL – see error below)
Failure when client is standard domain joined client
Failure when client is Azure domain joined client
Domain joined client connecting to non-Azure SQL hosted on same domain
- Active Directory Integrated (PASS)
- Windows Integrated (PASS)
- Active Directory Password (FAIL – Login failed for user ‘’)
- Active Directory Universal (FAIL – Login failed for user ’’)
sql-server 
azure-sql-database azure-active-directory asked Nov 9 at 20:59
Geekn
291314
add a comment |
up vote
0
down vote
favorite
So we have Azure AD synced with our on-premise domain. We have an Azure SQL Server configured with the active directory admin. We also have a non Azure SQL server running on a virtual machine in Azure that is domain joined to this same domain. The following are the results of using the various authentication mechanisms offered by SQL Management Studio (SSMS). Can someone explain why the failures occur with the various options that should be supported?
Facts about environment:
- Passthrough authentication is the sign-in method configured on AD Connect
- Password hash sync is also enabled so password hashes are stored in Azure AD
- Azure SQL is configured with Active Directory admin
- Latest version of SSMS was downloaded when performing these test
- On premise account was used to test all scenarios
Domain joined client connecting to Azure SQL from SSMS
- Active Directory Password (PASS)
- Active Directory Universal (PASS)
- Windows Integrated (FAIL - not supported by Azure SQL)
- Active Directory Integrated (FAIL – see error below)
Failure when client is standard domain joined client
Failure when client is Azure domain joined client
Domain joined client connecting to non-Azure SQL hosted on same domain
- Active Directory Integrated (PASS)
- Windows Integrated (PASS)
- Active Directory Password (FAIL – Login failed for user ‘’)
- Active Directory Universal (FAIL – Login failed for user ’’)
sql-server azure-sql-database azure-active-directory asked Nov 9 at 20:59
Geekn
291314
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
So we have Azure AD synced with our on-premise domain. We have an Azure SQL Server configured with the active directory admin. We also have a non Azure SQL server running on a virtual machine in Azure that is domain joined to this same domain. The following are the results of using the various authentication mechanisms offered by SQL Management Studio (SSMS). Can someone explain why the failures occur with the various options that should be supported?
Facts about environment:
- Passthrough authentication is the sign-in method configured on AD Connect
- Password hash sync is also enabled so password hashes are stored in Azure AD
- Azure SQL is configured with Active Directory admin
- Latest version of SSMS was downloaded when performing these test
- On premise account was used to test all scenarios
Domain joined client connecting to Azure SQL from SSMS
- Active Directory Password (PASS)
- Active Directory Universal (PASS)
- Windows Integrated (FAIL - not supported by Azure SQL)
- Active Directory Integrated (FAIL – see error below)
Failure when client is standard domain joined client
Failure when client is Azure domain joined client
Domain joined client connecting to non-Azure SQL hosted on same domain
- Active Directory Integrated (PASS)
- Windows Integrated (PASS)
- Active Directory Password (FAIL – Login failed for user ‘’)
- Active Directory Universal (FAIL – Login failed for user ’’)
sql-server azure-sql-database azure-active-directory asked Nov 9 at 20:59
Geekn
291314
So we have Azure AD synced with our on-premise domain. We have an Azure SQL Server configured with the active directory admin. We also have a non Azure SQL server running on a virtual machine in Azure that is domain joined to this same domain. The following are the results of using the various authentication mechanisms offered by SQL Management Studio (SSMS). Can someone explain why the failures occur with the various options that should be supported?
Facts about environment:
- Passthrough authentication is the sign-in method configured on AD Connect
- Password hash sync is also enabled so password hashes are stored in Azure AD
- Azure SQL is configured with Active Directory admin
- Latest version of SSMS was downloaded when performing these test
- On premise account was used to test all scenarios
Domain joined client connecting to Azure SQL from SSMS
- Active Directory Password (PASS)
- Active Directory Universal (PASS)
- Windows Integrated (FAIL - not supported by Azure SQL)
- Active Directory Integrated (FAIL – see error below)
Failure when client is standard domain joined client
Failure when client is Azure domain joined client
Domain joined client connecting to non-Azure SQL hosted on same domain
- Active Directory Integrated (PASS)
- Windows Integrated (PASS)
- Active Directory Password (FAIL – Login failed for user ‘’)
- Active Directory Universal (FAIL – Login failed for user ’’)
sql-server azure-sql-database azure-active-directory sql-server azure-sql-database azure-active-directory asked Nov 9 at 20:59
Geekn
291314
asked Nov 9 at 20:59
Geekn
291314
asked Nov 9 at 20:59
Geekn
291314
asked Nov 9 at 20:59
Geekn
291314
asked Nov 9 at 20:59
Geekn
291314
291314
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
This issue may be related with the AD Syncing options. Verify in your environment that AD is not syncing passwords into the tenant. This prevents AD Integration Authentication and AD Password Authentication. The only authentication that works on these cases is AD Universal Authentication, as your test shows.
answered Nov 9 at 22:12
Alberto Morillo
6,1371616
AD Connect sign-in method is set to passthrough authentication, but AD Connect is also syncing password hashes as well in case on-premise is not reachable and we need to manually switch to password sync. Password hash sync was also necessary because passthrough authentication does not currently work when trying to RDP into azure virtual machines in which case it will use the password hash stored in Azure AD. We are going to try and change the sign-in method to password hash and see if this changes out outcomes. Weird that results differ based on weather or not it's an Azure SQL instance.
– Geekn
Nov 9 at 22:55
Please consider also what Mirek is saying. It could also be related to the ADAL library in use. An old ADASQL.dll.
– Alberto Morillo
Nov 10 at 3:05
add a comment |
up vote
0
down vote
It looks to me that his is an ADAL problem (WSTrust) related to network configuration. Please create a support case and work with the support team to solve this problem
answered Nov 9 at 22:29
MirekS
1
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
This issue may be related with the AD Syncing options. Verify in your environment that AD is not syncing passwords into the tenant. This prevents AD Integration Authentication and AD Password Authentication. The only authentication that works on these cases is AD Universal Authentication, as your test shows.
answered Nov 9 at 22:12
Alberto Morillo
6,1371616
AD Connect sign-in method is set to passthrough authentication, but AD Connect is also syncing password hashes as well in case on-premise is not reachable and we need to manually switch to password sync. Password hash sync was also necessary because passthrough authentication does not currently work when trying to RDP into azure virtual machines in which case it will use the password hash stored in Azure AD. We are going to try and change the sign-in method to password hash and see if this changes out outcomes. Weird that results differ based on weather or not it's an Azure SQL instance.
– Geekn
Nov 9 at 22:55
Please consider also what Mirek is saying. It could also be related to the ADAL library in use. An old ADASQL.dll.
– Alberto Morillo
Nov 10 at 3:05
add a comment |
up vote
0
down vote
This issue may be related with the AD Syncing options. Verify in your environment that AD is not syncing passwords into the tenant. This prevents AD Integration Authentication and AD Password Authentication. The only authentication that works on these cases is AD Universal Authentication, as your test shows.
answered Nov 9 at 22:12
Alberto Morillo
6,1371616
AD Connect sign-in method is set to passthrough authentication, but AD Connect is also syncing password hashes as well in case on-premise is not reachable and we need to manually switch to password sync. Password hash sync was also necessary because passthrough authentication does not currently work when trying to RDP into azure virtual machines in which case it will use the password hash stored in Azure AD. We are going to try and change the sign-in method to password hash and see if this changes out outcomes. Weird that results differ based on weather or not it's an Azure SQL instance.
– Geekn
Nov 9 at 22:55
Please consider also what Mirek is saying. It could also be related to the ADAL library in use. An old ADASQL.dll.
– Alberto Morillo
Nov 10 at 3:05
add a comment |
up vote
0
down vote
up vote
0
down vote
This issue may be related with the AD Syncing options. Verify in your environment that AD is not syncing passwords into the tenant. This prevents AD Integration Authentication and AD Password Authentication. The only authentication that works on these cases is AD Universal Authentication, as your test shows.
answered Nov 9 at 22:12
Alberto Morillo
6,1371616
This issue may be related with the AD Syncing options. Verify in your environment that AD is not syncing passwords into the tenant. This prevents AD Integration Authentication and AD Password Authentication. The only authentication that works on these cases is AD Universal Authentication, as your test shows.
answered Nov 9 at 22:12
Alberto Morillo
6,1371616
answered Nov 9 at 22:12
Alberto Morillo
6,1371616
answered Nov 9 at 22:12
Alberto Morillo
6,1371616
answered Nov 9 at 22:12
Alberto Morillo
6,1371616
6,1371616
AD Connect sign-in method is set to passthrough authentication, but AD Connect is also syncing password hashes as well in case on-premise is not reachable and we need to manually switch to password sync. Password hash sync was also necessary because passthrough authentication does not currently work when trying to RDP into azure virtual machines in which case it will use the password hash stored in Azure AD. We are going to try and change the sign-in method to password hash and see if this changes out outcomes. Weird that results differ based on weather or not it's an Azure SQL instance.
– Geekn
Nov 9 at 22:55
Please consider also what Mirek is saying. It could also be related to the ADAL library in use. An old ADASQL.dll.
– Alberto Morillo
Nov 10 at 3:05
add a comment |
AD Connect sign-in method is set to passthrough authentication, but AD Connect is also syncing password hashes as well in case on-premise is not reachable and we need to manually switch to password sync. Password hash sync was also necessary because passthrough authentication does not currently work when trying to RDP into azure virtual machines in which case it will use the password hash stored in Azure AD. We are going to try and change the sign-in method to password hash and see if this changes out outcomes. Weird that results differ based on weather or not it's an Azure SQL instance.
– Geekn
Nov 9 at 22:55
Please consider also what Mirek is saying. It could also be related to the ADAL library in use. An old ADASQL.dll.
– Alberto Morillo
Nov 10 at 3:05
AD Connect sign-in method is set to passthrough authentication, but AD Connect is also syncing password hashes as well in case on-premise is not reachable and we need to manually switch to password sync. Password hash sync was also necessary because passthrough authentication does not currently work when trying to RDP into azure virtual machines in which case it will use the password hash stored in Azure AD. We are going to try and change the sign-in method to password hash and see if this changes out outcomes. Weird that results differ based on weather or not it's an Azure SQL instance.
– Geekn
Nov 9 at 22:55
AD Connect sign-in method is set to passthrough authentication, but AD Connect is also syncing password hashes as well in case on-premise is not reachable and we need to manually switch to password sync. Password hash sync was also necessary because passthrough authentication does not currently work when trying to RDP into azure virtual machines in which case it will use the password hash stored in Azure AD. We are going to try and change the sign-in method to password hash and see if this changes out outcomes. Weird that results differ based on weather or not it's an Azure SQL instance.
– Geekn
Nov 9 at 22:55
Please consider also what Mirek is saying. It could also be related to the ADAL library in use. An old ADASQL.dll.
– Alberto Morillo
Nov 10 at 3:05
Please consider also what Mirek is saying. It could also be related to the ADAL library in use. An old ADASQL.dll.
– Alberto Morillo
Nov 10 at 3:05
add a comment |
up vote
0
down vote
It looks to me that his is an ADAL problem (WSTrust) related to network configuration. Please create a support case and work with the support team to solve this problem
answered Nov 9 at 22:29
MirekS
1
add a comment |
up vote
0
down vote
It looks to me that his is an ADAL problem (WSTrust) related to network configuration. Please create a support case and work with the support team to solve this problem
answered Nov 9 at 22:29
MirekS
1
add a comment |
up vote
0
down vote
up vote
0
down vote
It looks to me that his is an ADAL problem (WSTrust) related to network configuration. Please create a support case and work with the support team to solve this problem
answered Nov 9 at 22:29
MirekS
1
It looks to me that his is an ADAL problem (WSTrust) related to network configuration. Please create a support case and work with the support team to solve this problem
answered Nov 9 at 22:29
MirekS
1
answered Nov 9 at 22:29
MirekS
1
answered Nov 9 at 22:29
MirekS
1
answered Nov 9 at 22:29
MirekS
1
1
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53233203%2fssms-and-active-directory-authentication-doesnt-work-with-azure-sql-and-non-azu%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
