Sql-server – Log report ‘bad password’, but still grants access

Linked Servers are not database specific, but provide a pipeline between two servers. Of course, the rights granted to the users will control which databases they can access. The account you are using naturally needs to be able to logon to both servers with enough permissions to execute your query.

Yes, creating a Linked Server is an administrative function and not available to all. Doing it yourself requires the ALTER ANY LINKED SERVER permission.

The parameters for the sp_addlinkedserver stored procedure are found at: http://msdn.microsoft.com/en-us/library/ms190479(v=sql.110).aspx

Also, under that link are instructions for several different ways to connect the servers together. If Server X and Server A are both SQL Servers, the setup is quite simple.

You can use sp_addlinkedsrvlogin to manage how the logins authenticate across the linked server. The details are at: http://msdn.microsoft.com/en-us/library/ms189811(v=sql.110).aspx

Depending on your landscape and if you are using Windows Logins, you may need to have Kerberos configured properly on your landscape so that the Kerberos ticket (allowed permissions) can be forward to the other server. See: Configuring Linked Servers for Delegation:

http://technet.microsoft.com/en-us/library/ms189580(v=sql.105).aspx

The information being requested here is not SQL Server information. It is Windows / OS -level information. This information is not available within SQL Server (i.e. there does not appear to be any DMV containing this info).

It should also be noted that the Login properties of is_policy_checked and is_expiration_checked only pertain to SQL Server Logins, not Windows Logins (since Windows Logins already have those enforced as per system / Domain policy).

The only way to get the password policy information is to get it from the Operating System. You can either use a GUI or command-line NET ACCOUNTS as follows:

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

By default, NET ACCOUNTS displays information for the local system. If your computer is attached to a Domain and your login is a Domain account, then you need to add the /DOMAIN switch:

C:\>NET ACCOUNTS /DOMAIN

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

Of course, if you don't have permission to see that information, then you need to contact a Domain Administrator.