SSPI handshake failed SQL Server Error: 17806, Severity: 20, State: 14.

 If you are using Windows authentication to connect to a SQL Server in Windows 2003 and above, you may encounter the following error.

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 192.168.1.20].

Error: 17806, Severity: 20, State: 14.

This error can happen due to Active Directory configuration issue or Network related issues. Assuming they are fine, another reason could be related to failed Kerberos Authentication to connect to SQL Server.

In Windows 2003 and above, the default method of establishing a connection is Kerberos but if it fails, then connection may be established using NTLM. An error may be registered on SQL Server error log due to Kerberos failure.

To confirm if you have any database connection using Kerberos authentication, run this on your SQL Server

SELECT distinct auth_scheme FROM sys.dm_exec_connections

If you only see NTLM and SQL then, there is no Kerberos connection.

To use Kerberos, the client and SQL Server must be in the same domain or trusted domains and a Service Principal Name (SPN) must be registered with Active Directory for SQL Server service account.

You can check if the service account already has SPN, by running this on the SQL Server

setspn -l <domain\sqlserviceaccount>

If you don't see anything starting with MSSQLSvc\ then the account is not setup with SPN.

You can manually register SPN as a Domain Administrator using these commands.

• To create an SPN for the NetBIOS name of the SQL Server use the following command: setspn –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>
• To create an SPN for the FQDN of the SQL Server use the following command: setspn -A MSSQLSvc/<SQL Server FQDN>:1433 <Domain\Account>

If SQL Server service account is granted "Validated write to service principal name" privilege in Active Directory, SQL Server database engine will register itself when it starts and unregisters at shutdown. If you Domain Administrators do not permit this setting then you should manually register the SPN and the SSPI error will go away.

Also the SQL Server service account must be granted "Account is trusted for delegation" privilege in Active Directory. If your SQL Server connects to other SQL Server using Linked Server then the server must also be granted "Trust this computer for delegation to any service" privilege.

For a named instance, you can use the FQDN of the named instance instead of SQL Server FQDN and use the port number.

For a clustered server, you should use the FQDN of the SQL Server virtual name. In this case you will need two entries, one with port name and one without such as

setspn -A MSSQLSvc/<Virtual SQL Server FQDN> <Domain\Account>
setspn -A MSSQLSvc/<Virtual SQL Server FQDN>:1433 <Domain\Account>

Kerberos authentication works when SQL Server, Service Account and Windows account accessing the SQL Server are in same windows domain. If anyone these are in different domain, there must be bidirectional trust established between the domains, otherwise it does not work.



 

Latin (UTF) Characters in Oracle Database

Oracle is capable of storing many different characters, however a confusion arises when you need to work with mixed character sets.

Different settings in both Oracle database and client program govern how characters are handled by Oracle. There are many detailed white papers available at support.oracle.com, I am just trying to simplify it based on my recent experience of INSERTing and SELECTing Latin characters.

To find out if your database can handle a character set, check NLS_CHARACTERSET parameter

select * from nls_database_parameters;

This parameter dictates what you can put in CHAR or VARCHAR2 columns. This setting should typically be set to the characters used most commonly in your database.

Another parameter to look at is NLS_NCHAR_CHARACTERSET. This determines what can you store in NCHAR or NVARCHAR2 columns.

So the previous two settings are on the database side. Next you should look at client side setting that influences the behavior of your query. For example, if you use a Windows graphical tool such as Toad to INSERT and SELECT from a NCHAR column, the data looks fine as long as you are in Toad. However if you query the same data from SQL Plus on command prompt you may see different result.

To overcome this issue, you need to look at NLS_LANG setting on the client machine. In Windows machine it can be set in Registry as well as System Environment variable. You have to set both if you plan to use GUI or non-GUI interface.

The registry key location varies by Oracle client version but it is in

HKEY_LOCAL_MACHINE\Software\Oracle\Key_<oracle_home_name>

for Oracle 10g and up.

In my machine the value is set as AMERICAN_AMERICA.WE8MSWIN1252

To use SQL Plus from command prompt, I needed to set NLS_LANG in System Environment variable to a equivalent character set as

AMERICAN_AMERICA.US8PC437

To do the same with Putty/ssh session to a Linux database server, run

$locale

Check if all LC_ values are compatible with the language you are trying to work with. In my case it was all en_US.UTF-8

The LC_ALL was empty, so I set that to the same value

export LC_ALL=en_US.UTF-8

Next as in Windows I had to set NLS_LANG to right value to support the Latin characters

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

This should resolve all character conversion issues. If it is still not working for you, then Putty may be doing character conversion. You can check Putty Configuration - Window - Translation - Received data assumed to be in which character set, change this to UTF-8 and you are good to go.

SQL Server: Worker x000 appears to be non-yielding on Scheduler x

SQL Server can become unresponsive with non-yielding worker process. When this happens you can't even kill it as this is a system process.

The following Microsoft KB article lists a hot fix as well as a work around for the condition.

http://support.microsoft.com/kb/2491214


We encounter this error twice. First was with Quest LiteSpeed product. Since LiteSpeed runs as a process withing SQLSERVR.EXE and executes extended stored procedures. If there is any issue with LiteSpeed DLL then it hogs the SQL process and the server hangs.

To resolve this, we used the second work around of setting SuperSocketNetlib\Tcp\TcpAbortiveClose to 1 as the SQL Server patch was already applied and it did not help.

When we encountered it again, Microsoft suggested us to apply a patch to .Net, it seems there is an issue with .Net garbage collector that can contribute to this condition.


http://support.microsoft.com/kb/2504603

Change Oracle Sequence Min Value: ORA-04007

The reason I had to change the NEXTVAL of the sequence was the column using the sequence value as the Primary Key. The data in the table was imported from other environment where the sequence value was higher than in this server, so when application tried to add data into table, the sequence value was less than data value causing Unique constraint error.

ORA-00001: unique constraint (owner.table_name) violated

Oracle does not allow changing the MINVALUE of a sequence, if you do

ALTER SEQUENCE myseq MINVALUE 100;

You will get

ORA-04007: MINVALUE cannot be made to exeed the current value

There are three ways to reseed the current value.

From SQL*Plus execute

SELECT myseq.nextval FROM DUAL;

till you reach the desired value.

Drop the sequence and recreate it using the desired MINVALUE

DROP SEQUENCE myseq;

CREATE SEQUENCE myseq
  START WITH 100
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

Change the INCREMENT BY value temporarily, generate a sequence and set INCREMENT BY value back to 1. First you need to know the current value, which you can get it from DBA_SEQUENCE table or by generating new sequence

SELECT myseq.nextval FROM DUAL;

Next, alter the sequence to increment by desired value. For example, if my current nextval is 40 and I want to change it to 100, I will alter the sequence as

ALTER SEQUENCE myseq INCREMENT BY 60;

Then generate new sequence

SELECT myseq.NEXTVAL FROM dual;

  

ALTER SEQUENCE myseq INCREMENT BY 1;

 

SQL Server Linked Server: Error "Invalid Authorization Specification" (Microsoft SQL Server Error: 7399)

I created a linked server to another SQL Server and to limit access to the Linked Server, I used Local Server Login mapping to Remote Server mapping option and for the login not listed in the mapping, I picked "Be made without using a security context" option.

When I do this as sa, since even sa is not allowed to use the linked server, I get an error at the end of creation wizard. I accept the error and continue.

The following error message is generated if you go back to update the properties and save.
















But this is an expected behavior. Now if you connect to the SQL Server using the login mapped in the previous step, and try to browse the linked server catalog, you will get this error

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)



Howver, notice that you can run a query against the linked server from Management Studio. The error message only appears if you try to browse the catalog. If your users are only querying the linked server using T-SQL then you can ignore this error, otherwise you can grant access to mater database to the user and additionally grant execute on the procedure mentioned in the the error to the user.

USE master
GO
CREATE USER youruser TO LOGIN youruser
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO youruser