701
SQLServer 2012 登陸異常問題
背景
RDS FOR SQLServer 2012 已經上線一段時間了，從反饋來看大家遇到了很多權限相關的問題，所以建議把RDS FOR SQLServer 2012權限提升這篇文章作為基礎知識先能夠理解；再之後針對大家經常遇到的CASE我們會把解決方法分享出來，本篇文章是其中之一。
問題
用戶應用鏈接數據庫失敗 持續報錯，業務因此停滯，使用SSMS鏈接也出錯
Detail
===================================
Cannot connect to ***
===================================
Cannot open user default database. Login failed.
Login failed for user 'option_u'. (.Net SqlClient Data Provider)
------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476
------------------------------
Server Name: ***
Error Number: 4064
Severity: 11
State: 1
Line Number: 65536
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 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()
解決
option_u是用戶的默認賬號，根據報錯判斷是option_u賬號的默認數據庫出現異常，為了確認我們通過後端Windows認證登陸用如下SQL判斷下：
SELECT
SP.NAME,
SP.TYPE_DESC,
DB.STATE_DESC
FROM SYS.SERVER_PRINCIPALS AS SP
INNER JOIN SYS.DATABASES AS DB
ON SP.DEFAULT_DATABASE_NAME = DB_NAME(DB.DATABASE_ID)
WHERE SP.NAME = 'OPTION_U'
結果沒有返回！那這樣看來是用戶設置了一個不存在的數據庫作為默認數據庫，修複方式如下：
USE [master]
GO
ALTER LOGIN [option_u] WITH DEFAULT_DATABASE=[master]
GO
注意修複的前提是用戶首先要能登陸數據庫，當然著急的情況通過工單讓後端處理也是可行的，但實際如果手邊有SSMS客戶端(如果沒有可以從 這裏下載)可以通過指定數據庫解決
觸發場景
問題的解決方法大家已經清楚了，但為什麼會出現這種情況即用戶為什麼要設置一個不存在的庫作為默認數據庫呢？
為了了解背後的原因我們了解了用戶的場景並做了如下測試：
實例生產後在控製台創建初始賬號 test
-
利用初始賬號登陸登陸實例並創建數據庫testdb
SELECT SUSER_NAME() AS LOGIN_NAME GO CREATE DATABASE TESTDB GO
-
把test的默認數據庫改為testdb
USE [master] GO ALTER LOGIN [test] WITH DEFAULT_DATABASE=[testdb] GO -
刪除testdb
DROP DATABASE [testdb]
再次嚐試test登陸
至此原因也清楚了，用戶並不是刻意設置一個不存在的數據庫而是開始存在後來刪除了
這裏還有2個步驟值得分析和改進
-
步驟3用戶為什麼要把test賬號的默認數據庫改為testdb
這實際並不是一個通用的需求完全取決於應用，修改默認數據庫後用戶的鏈接字符串不需要配置DB參數，應用鏈接數據庫後可以直接訪問testdb下的object；反之也可以通過修改鏈接串的DB參數或者應用訪問數據庫對象的寫法比如是DBNAME.Schema.Object（不支持link所以不需要servername），但這涉及的應用修改所以需要用戶判斷哪種更合適業務
-
步驟4刪除testdb
我們知道SQLServer在一些情況會檢查依賴關係，比如要刪除某個賬號但此賬號在Trigger中有用到，那麼刪除就會失敗並提示有引用依賴，但看起來login的default database沒有在考慮範圍內，微軟雖然沒做但考慮雲產品的受眾RDS可以通過一些方式實現給予用戶更好的引導以提升體驗，這也會作為後續的一個可改進點
最後更新：2017-05-08 15:32:33