Tag Archives: SQL Server

Adding a ‘self’ linked SQL Server (SQL Server R2)

To mimic a multi-SQL Server environment of one of our clients on a single server, I needed to link the SQL Server to itself. If found several suggestions by googling it, but only this one worked for me:

EXEC sp_addlinkedserver @server=’alias’, –the name of the linked server
@srvproduct=”,
@provider=’SQLNCLI’,
@datasrc=’.’,–the data source
@provstr=’Integrated Security=SSPI';

The other suggestions I found, used the same approach, but with a different provider. SQLNCLI worked for me.

SQL Server 2005 ‘Login failed’ (error 18456 state 16)

The event log of one of our SQL Servers filled up with events 18456 (every minute).

Failure Audit: Login failed for user ‘\’. [CLIENT:]

Looking in the SQL Server log, I found some extra information.

2011-10-18 12:52:00.55 Logon       Error: 18456, Severity: 14, State: 16.

I found this MSDN blog post, explaining most of the State codes, except for state 16.

ERROR STATE

ERROR DESCRIPTION

2 and 5

Invalid userid

6

Attempt to use a Windows login name with SQL Authentication

7

Login disabled and password mismatch

8

Password mismatch

9

Invalid password

11 and 12

Valid login but server access failure

13

SQL Server service paused

18

Change password required

Digging a little further, I found that State 16 usually means that the target database cannot be located. I most cases, this is due to an SQL Server Job that targets a deleted of offline database.
Deleting or disabling the job gets rid of the errors in the event log.