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: http://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客户端(如果没有可以从 这里下载)可以通过指定数据库解决

触发场景

问题的解决方法大家已经清楚了，但为什么会出现这种情况即用户为什么要设置一个不存在的库作为默认数据库呢？

为了了解背后的原因我们了解了用户的场景并做了如下测试：

1. 实例生产后在控制台创建初始账号 test
   
2. 利用初始账号登陆登陆实例并创建数据库testdb

   SELECT SUSER_NAME() AS LOGIN_NAME
   GO
   CREATE DATABASE TESTDB
   GO
   

   

3. 把test的默认数据库改为testdb

   USE [master]
   GO
   ALTER LOGIN [test] WITH DEFAULT_DATABASE=[testdb]
   GO
   

4. 删除testdb

   DROP DATABASE [testdb]
   

   

5. 再次尝试test登陆

至此原因也清楚了，用户并不是刻意设置一个不存在的数据库而是开始存在后来删除了

这里还有2个步骤值得分析和改进

• 步骤3用户为什么要把test账号的默认数据库改为testdb

  这实际并不是一个通用的需求完全取决于应用，修改默认数据库后用户的链接字符串不需要配置DB参数，应用链接数据库后可以直接访问testdb下的object；反之也可以通过修改链接串的DB参数或者应用访问数据库对象的写法比如是DBNAME.Schema.Object（不支持link所以不需要servername），但这涉及的应用修改所以需要用户判断哪种更合适业务

• 步骤4删除testdb

  我们知道SQLServer在一些情况会检查依赖关系，比如要删除某个账号但此账号在Trigger中有用到，那么删除就会失败并提示有引用依赖，但看起来login的default database没有在考虑范围内，微软虽然没做但考虑云产品的受众RDS可以通过一些方式实现给予用户更好的引导以提升体验，这也会作为后续的一个可改进点