主页 / dba / 问题 / 118136
Accepted
Md Haidar Ali Khan
Asked: 2015-10-16 05:53:15 +0800 CST

是否可以通过 T-SQL 知道剩余的“sql 登录密码过期日期”和“密码最长使用期限”

  • 772

我想说，今天我遇到的任何与 Microosft SQL Server 2012 相关的事件。我在办公时间的开始工作时间登录了 Microsoft SQL Server 2012，但是在我公司工作时间的中间，我得到了（ Microsoft SQL Server，错误：18487) 类似错误消息

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

Cannot show requested dialog.

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

Cannot show requested dialog. (SqlMgmt)

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

   at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument initializationXml, 

IServiceProvider dialogServiceProvider, CDataContainer dc)
   at 

Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAll

ocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider)
   at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
   at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolMenuItemHelper.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
   at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

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

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

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

   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLogin.CheckObjects(String login)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateLogin..ctor(CDataContainer context)

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

Failed to connect to server ServerName. (Microsoft.SqlServer.ConnectionInfo)

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

   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.get_DatabaseEngineType()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.UpdateConnectionInfoIfCloud(Object& connectionInfo, Urn urn, Boolean& isUpdated)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

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

Login failed for user 'SQLLoginID'.  Reason: The password of the account has expired. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18487&LinkId=20476

------------------------------
Server Name: ServerName
Error Number: 18487
Severity: 14
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.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

我已经在 stackExchange 站点中浏览了与此主题相关的所有内容。主要是 Aaron Bertrand 所有与此主题相关的博客文章，例如Does sys.sql_logins.is_policy_checked 是否意味着该策略已被检查？和验证现有用户的密码策略以及https://dba.stackexchange.com/search?q=password+policy。

但我想问这个问题，这就是为什么要问 sql 专家。

与 SQL Server 专家 Aaron Bertrand 和其他我的高级 StackExchange SQL 专家相比，我是 SQL Server 的新手。根据工作环境政策，系统管理员没有共享“本地安全设置策略”。所以，我没有确切的“密码最长使用期限”到期信息。我已经多次遇到上述与更改密码有关的错误。

从 T-SQL 查询

select * from sys.sql_logins;

在此查询之后，我知道所有 sql syslogin 详细信息，例如is_policy_checked、is_expiration_checked、create_date和modify_date。

从 modify_date 我知道 sql 登录修改日期的确切日期。此外，从上面的查询可以清楚地看出哪个用户应用了“强制密码策略”和“强制密码过期”以及他们的 SQL 登录。

在我的环境中，我有一个 Microsoft SQL Server 2012 的“sa”用户。但我无权检查本地安全设置->帐户策略->最大密码年龄。

任何与此错误相关的提示或线索将不胜感激。

security sql-server-2012

1 个回答

  1. Best Answer

    此处请求的信息不是 SQL Server 信息。它是 Windows / OS 级别的信息。此信息在 SQL Server 中不可用（即似乎没有任何 DMV 包含此信息）。

    还应该注意的是，登录属性is_policy_checked仅is_expiration_checked适用于 SQL Server 登录，而不是 Windows 登录（因为 Windows 登录已经根据系统/域策略强制执行了这些属性）。

    获取密码策略信息的唯一方法是从操作系统中获取。您可以使用 GUI 或命令行NET ACCOUNTS，如下所示：

    C:\>NET ACCOUNTS

Force user logoff how long after time expires?:       Never
Minimum password age (days):                          0
Maximum password age (days):                          42
Minimum password length:                              0
Length of password history maintained:                None
Lockout threshold:                                    Never
Lockout duration (minutes):                           30
Lockout observation window (minutes):                 30
Computer role:                                        WORKSTATION

    默认情况下，NET ACCOUNTS显示本地系统的信息。如果您的计算机连接到域并且您的登录是域帐户，那么您需要添加/DOMAIN开关：

    C:\>NET ACCOUNTS /DOMAIN

The request will be processed at a domain controller for domain {computers_domain_name}.

    当然，如果您无权查看该信息，则需要联系域管理员。

    • 2

相关问题