无法通过SQL Server Management Studio连接Azure SQL数据库问题咨询

可通过Liquibase使用jtds驱动正常连接Azure SQL Server并执行查询，但使用相同凭据尝试通过SQL Server Management Studio连接时失败，连接配置如下：

• Server Type: Database Engine
• Server Name: myserver.database.windows.net,1433
• Authentication: SQL Server authentication
• Login: 已正确填写对应用户名
• Password: 已正确填写对应密码

报错信息如下：

无法连接到 myserver.database.windows.net,1433。
用户 'username' 登录失败。(.Net SqlClient Data Provider)
Server Name: myserver.database.windows.net,1433
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()

该问题核心是SSMS使用的.Net SqlClient驱动与jTDS驱动的登录逻辑存在差异，以下是具体原因和对应解决步骤：

核心原因 #

• jTDS驱动对Azure SQL用户名的格式要求宽松，仅填写用户名本身即可正常登录，但.Net SqlClient驱动强制要求SQL身份验证的用户名必须带上Azure SQL服务器前缀，否则直接触发18456状态1的登录失败错误
• 部分旧版本SSMS默认加密配置与Azure SQL要求不匹配，也会导致登录失败

解决步骤 #

1. 修正用户名格式：登录框的用户名输入栏不要只填账号本身，改为账号@服务器前缀格式，服务器前缀为Server Name中.database.windows.net前的内容，例如服务器是myserver.database.windows.net，账号是admin，就填写admin@myserver
2. 调整加密配置：点击SSMS连接窗口右下角的「选项」按钮，切换到「连接属性」标签页，勾选「加密连接」，测试环境可临时勾选「信任服务器证书」，再次尝试连接即可
3. 版本兼容排查：如果上述操作后仍连接失败，卸载当前旧版SSMS，安装最新版本SSMS即可解决驱动兼容问题

内容的提问来源于stack exchange，提问作者Ajay K