Azure SQL不允许已创建客户端登录

Question

我使用Azure Data Studio中的数据库中的免费帐户，按照推荐的过程为Azure SQL创建了几个用户：

• 将客户端ip地址添加到防火墙
• 在master数据库上创建登录
• 创建用户
• 添加至少包含db_accessadmin的适当角色

尽管如此，除非我使用安装时创建的管理员帐户，否则我尝试使用Azure Data Studio和SSMS登录仍会失败。唯一的eror信息是：

登录失败的

我正在尝试从我的ip地址进行连接，甚至将我的ip地址添加到数据库防火墙。我是不是错过了什么？

编辑

在master上运行命令以创建用户[用户名和密码已更改]：

CREATE LOGIN userlogin WITH PASSWORD='my_password';

在数据库上运行的命令：

CREATE USER myuser FROM LOGIN userlogin;
ALTER ROLE db_datawriter ADD MEMBER myuser;
ALTER ROLE db_datareader ADD MEMBER myuser;
ALTER ROLE db_accessadmin ADD MEMBER myuser;

Azure Data On尝试访问数据库的详细信息：

Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'myuser'.
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, SqlAuthenticationProviderManager sqlAuthProviderManager)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass40_0.<TryGetConnection>b__1(Task`1 _)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<<OpenAsync>b__0>d.MoveNext() in D:a1ssrcMicrosoft.SqlTools.ManagedBatchParserReliableConnectionReliableSqlConnection.cs:line 314
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:a1ssrcMicrosoft.SqlTools.ServiceLayerConnectionConnectionService.cs:line 549
ClientConnectionId:0682c1d3-d252-4379-b0b2-c00364e90356
Error Number:18456,State:1,Class:14

编辑2

好吧，经过一番挠头之后，我找到了SSMS。使用相同的命令，它就像一个符咒。不确定，但默认命令在角色和角色用户之前有一个N。它还将从登录切换到用于登录我只是在Azure Data Studio上呆了一段时间。

Answer 1

问题是您不小心使用了FOR而不是From。我看到的大多数文档都表明，这并不重要。我唯一能想到的是，如果你有评论部分[https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15#备注]

该示例显式使用from，然后还有其他引用from外部提供程序。我想知道AD是否想要From。

可能只是矩阵中的一个小问题

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-服务器-ver15

我很高兴你能帮上忙。