SQL Server - SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed

This error is reported on a server with SQL Server installed :

 

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxxxxxxxxx]

 

Based on experience, this error occurs under different circumstances and can be tricky to diagnose. I’ve had to troubleshoot and issue various corrective mesaures:

1) If a Windows service account is used and the password changes. This is a common reason.

2) The SPNs are invalid. An SPN is the Server Principal Name utilized by Kerberos authentication.  In that case , delete the MSSQL SVC SPN and issue a SQL Server restart. This registers immediately.

3)Rights have been denied , analyse the local security policies.

Try running gpresult. Gpresult displays the Group Policy Settings and Resultant Set of Policy (RSOP) for the server. It should also tell you any problems/errors that it found when processing the GPOs.

 4)Check communications with AD .

 5) Is the server recognised?

Read More

Event ID 40960 Cannot generate SSPI context - SQL Server DBA

Database Connectivity test with Powershell - SQL Server DBA

SQL Server - SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security

SQL Server – DBCC SQLPERF(netstats) and network activity

A useful undocumented SQL Server command is DBCC sqlperf (netstats). It allows the DBA to monitor and report usage of network activity of the SQL Server Instance.

Reporing on network usage is useful in different situations, such as analysing communication between SQL Server and the the network

 The command are :

 

--issue the commnand to view the details
DBCC SQLPERF('netstats')

--clear existing statistics to start a new count
DBCC SQLPERF ('netstats', CLEAR);

 The output is :

Statistic |          Value

Network Reads  2.0427E+07

Network Writes  3.20437E+07

Network Bytes Read      -1.322977E+09

Network Bytes Written   -2.034788E+08

Command Queue Length            0

Max Command Queue Length     0

Worker Threads 0

Max Worker Threads      0

Network Threads            0

Max Network Threads     0

Read More

SQL Server – netstat monitoring and tuning performance - SQL ...

 

SQL Server - How to find Open Transactions

SQL Server – Current queries executing on SQL Server

SQL Server – network packet size - SQL Server DBA

Speed and Duplex settings - SQL Server DBA

SQL Server – network packet size

The SQL Server network pack size is by default set at 4096  bytes. Depending on the operation different  values may give better performance – due to less reads and writes on the network.

Verify by checking  sp_configure  under the name “network packet size (B)”.  For certain circumstances , analysing the SQL Server network packet size is useful for troubleshooting. Before making any changes , consider the full range of traffic – and whether it will cause a performance improvement.

I’ve seen DBAs who habitually change the network packet size , for example on bulk copy operations , but I rarely change the setting , focusing on setting the connection string to send a certain packet size

I analysed a  problem recently where a network packet size of greater than 8000 , caused multi-page allocations in VAS.  This caused excessive VAS memory pressure and fragmentation leading to VAS filling  – and forced a SQL Server restart once a day.

To check the current network packet size of existing connections use

select * from Sys.dm_exec_connections

Monitor the network packet count for a specific connection by looking at num_reads and num_writes in sys.dm_exec_connections system view. For example:

 

select * from sys.dm_exec_connections where session_id=@@spid

Related Posts 

SQL Server - Sys.dm_os_memory_clerks and AWE memory allocation

SQL server OLE Automation memory pressure

SQL Server Performance ,memory pressure and memory usage

SQL Server – netstat monitoring and tuning performance

SQL Server – Windows Environment Variables and Powershell

Powershell allows you to access the Windows Environment Variables. This is very useful in collecting system information . I’ve applied this technique in  creating an inventory report or troubleshooting a server issue. The purpose of Environment variables is to set values  at the OS level – that will impact processes. Accessing and reporting on the variables quickly can assist in maintaining server uptime.

A straightforward way of accessing all the environment variables is to use  “Dir env:\”  . A full list of environment variables will appear.  Although the full list appears , it requires some string manipulation to isolate one variable and value. Powershell allows you to access the individual Environment variables .

When scripting , there is a requirement to access the environment variables individually. To access individually use  $env  followed by the Windows Environment variable . The example includes the common Environment Variables, although use     “Dir env:\” to view the full list of variables

$env:COMPUTERNAME
$env:NUMBER_OF_PROCESSORS
$env:OS
$env:PROCESSOR_ARCHITECTURE
$env:PROCESSOR_IDENTIFIER
$env:PROCESSOR_LEVEL
$env:PROCESSOR_REVISION
$env:SystemDrive
$env:SystemRoot
$env:USERDNSDOMAIN
$env:USERDOMAIN
$env:USERNAME
$env:USERPROFILE
$env:LOGONSERVER
$env:HOMEDRIVE
$env:SESSIONNAME
$env:windir

 Related Posts

Powershell Scripts for DBA

Powershell - run script on all sql servers

SQL Server Database Size and Free Disk Space Daily Report

SQL Server – DNS NSLOOKUP and Resolve IP

Resolving an IP address through NSLOOKUP is a useful  trick. The NSLOOKUP tool is normally used as a command line tool to test DNS servers. There are certain circumstances in SQL Server , where it can help resolve some problems

Xp_cmdshell needs to be available to the logon , which raises questions around security. It’s normally a privilege allocated on Production servers . Careful consideration should be given to enabling the xp_cmdshell. Most Production level SQL Server Security Policies  won’t allow this privilege.

In the example , SQL Server executes xp_cmdsell , the results go into a temp table. Some string manipulation may be needed to return substrings.

I used this code to implement a Logon Trigger – to resolve an ip address. Passing the ip address returned by the ClientHost parameter – to return the computer name of the  client request and block the user from creating a user session.

 

DECLARE @xpCmd NVARCHAR(100)
DECLARE @ip varchar(100)
SET @ip  = '10.100.144.60'
SET @xpCmd = 'NSLOOKUP ' + @ip

CREATE TABLE #tmp1 
(nslookup_out NVARCHAR(100))

INSERT INTO #tmp1
EXEC master.dbo.xp_cmdshell @xpCmd

SELECT * FROM #tmp1

DROP TABLE #tmp1

----output
---Server:  ddd.mydomain.sub.net
---Address:  11.111.121.51
---NULL
---Name:    server1.mydomain.sub.net
---Address:  10.100.144.60
---NULL
---NULL

 Related Posts

SQL Server – Restrict SQL Server Logons by IP with EVENTDATA and SQL Logon Trigger

How to create a SQL Server Security Audit