The Target Principal Name Is Incorrect. Cannot Generate SSPI Context

Setting up an instance of SQL Server 2008 R2 64bit Standard, got this error when trying to connect from off the server.

Still working to determine the cause, in the meantime was able to temporarily remedy by disabling names pipes and setting service account to "LocalSystem".

Connection String Variations

.\instance
(local)\instance
127.0.0.1\instance - loopback
servername\instance
ip\instance
ip,port
tcp:servername\instance - tcp/ip
lpc:servername\instance - shared memory
np:servername\instance - named pipes

SQL Server Network Client Utility 32/64bit

You can set up SQL Server aliases on a Windows Server or PC using the Network Client Utility even if they don't have the SQL Server Client tools installed.

The trick is to keep in mind that there are two different SQL Server Network Client Utility runtimes on a 64-bit machine.

If you are running a 32-bit Operating System, you will have only 32 bit drivers installed.

If you are using a 64 bit machine, the default utility will be for 64-bit data sources.

If you have a 64bit OS and are trying to access an alias in your 32bit application and receive the error check to see if you have an alias configured for the architecture of your application ( 32-bit / 64-bit ).

Use this utility to Review 64-Bit aliases and protocols
c:\windows\system32\cliconfg.exe

Use this utility to Review 32-bit aliases and protocols
c:\windows\sysWOW64\cliconfg.exe


Additional info:
http://geekswithblogs.net/twickers/archive/2009/12/08/136830.aspx

http://blogs.technet.com/b/meamcs/archive/2013/01/22/creating-a-sql-server-alias-using-the-sql-server-client-network-utility.aspx

http://www.sharepointassist.com/2010/02/02/configure-a-sql-server-alias-for-sharepoint-sql-server-2008/

Recovering access

Local admins can get into instance when sysadmin access has been lost by starting the server in single user mode.   Here is how recover a sysadmin account without having to restart.  Prior to 2008 BUILTIN\Administrators was by default a member of sysadmin.   In 2008/2008R2 that login was not added by default, but NT AUTHORITY\SYSTEM still is.

psexec -s -i cmd

sqlcmd

create login [login] from windows
go

exec [sys].[sp_addsrvrolemember] 'login', 'sysadmin';
go

Powershell ISE

Powershell ISE is not installed by default on Windows Server 2008 R2

To install it, launch Powershell and enter the following commands:

Import-Module ServerManager
Add-WindowsFeature PowerShell-ISE

Rebuild Indexes and Update Statistics

Rebuild Indexes, then update statistics.

An index rebuild also updates index statistics with 100% sample rate.

After an index rebuild, only statistics not updated by the index-rebuild need to be updated.

An index re-org/defrag does not update statistics

In SQL Server 2005+ sp_updatestats only updates the statistics that need to be updated.

How to get out of SINGLE_USER mode

USE [master] 
GO

     SELECT 'kill ' + cast(spid as varchar)
       FROM master.sys.sysprocesses
      WHERE dbid = DB_ID('dbname')
GO

     SELECT 'kill ' + cast(request_session_id as varchar)
       FROM   sys.dm_tran_locks
      WHERE resource_database_id = DB_ID('dbname') 
GO

     SELECT 'kill ' + cast(session_id as varchar)
       FROM sys.dm_exec_requests 
      WHERE database_id = DB_ID('dbname')
GO

--KILL any processes connected to dbname



SET DEADLOCK_PRIORITY HIGH
GO
exec sp_dboption '[dbname] ', 'single user', 'FALSE';
GO
ALTER DATABASE [dbname] SET MULTI_USER WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [dbname] SET READ_WRITE WITH NO_WAIT
GO