0

So I am helping with some work and we have built a few servers in Azure with their SQL server image. Everything seems healthy apart from when adding an AD group for the domain admins to access SQL i get the following error below. I can add the users in individually however thats not very practical.

AD group is Universal and a security group however i did also try creating a global group but that did not work either.

Server is configures with SQL and Windows Auth and SQL is 15.0.4188.2

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

Cannot connect to W-P-SQL-VM-04.

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

Login failed for user '****\RS_Admin'. (.Net SqlClient Data Provider)

------------------------------ For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

------------------------------ Server Name: W-P-SQL-VM-04 Error Number: 18456 Severity: 14 State: 1 Line Number: 65536

------------------------------ Program Location:

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Improve this question
2
  • 3
    You'll want to look at the state from the SQL Errorlog and not this error. Most likely it's a permissions issue as group logins have permissions applied from all relevant groups.
    – Sean Gallardy
    Dec 21, 2021 at 11:49
  • 1
    Use the user also a member of a group with a deny permission?
    – Brendan McCaffrey
    Dec 21, 2021 at 15:07

1 Answer 1

Reset to default
0

A very basic thing but when you say "AD", do you mean Azure AD (AAD) or on-prem AD? Azure SQL Database supports Azure AD, so if your group isn't available in AAD (e.g. it hasn't been synchronised from on-prem AD), that could be your problem.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.