Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Wednesday, April 12, 2017

Resolve SQL Server error “Cannot generate SSPI context” in a Kerberos environment

Recently I came across following error when I tried to create an ODBC connection to one of my SQL Servers using TCP/IP.

image

The SQL server belongs to a SharePoint farm which is configured with Kerberos authentication. The same SQL server was initially configured with local system account and later reconfigured with a domain account (e.g trs\SQL_Admin).

Logs showed that there is an issue with SPN records

Following are the steps I followed to resolve the issue

  • Check existing SPN records for the service account

setspn –l trs\sql_admin

It lists all SPN records registered. However it did not contain an entry for

mssqlsvc/TRS-AUS1-AS-01.trs.local:1433

  • Add missing SPN record using following command

setspn -a mssqlsvc/TRS-AUS1-AS-01.trs.local:1433 trs\sql_admin

Unfortunately I received an error saying that there is a duplicate record. Let’s investigate that

  • Execute following command to find out any SPN records for that particular server

setspn -l TRS-AUS1-AS-01

It had a record with same signature. May be it was created for my local service account when SQL server was initially configured

image

  • We need to remove those SPN entries. To remove them use following commands

SetSpn -d MSSQLSvc/TRS-AUS1-AS-01.TRS.local:1433 TRS-AUS1-AS-01
SetSpn -d MSSQLSvc/TRS-AUS1-AS-01.TRS.local TRS-AUS1-AS-01

  • Then we need to add new SPN entries with our service account

setspn -a mssqlsvc/TRS-AUS1-AS-01:1433 trs\sql_admin
setspn -a mssqlsvc/TRS-AUS1-AS-01.trs.local:1433 trs\sql_admin

  • You can verify the delegation through Active Directory Users and Computers

image

That’s all we have to do. Now I can create an ODBC connection using TCP/IP

Monday, April 25, 2016

Resolve error : “This operation can be performed only on a computer that is joined to a server farm by users who have permissions in SQL Server to read from the configuration database. To connect this server to the server farm, use the SharePoint Products Configuration Wizard, located on the Start menu in Microsoft SharePoint 2010 Products” in SharePoint 2013 farm

Recently I came across an error like this in one of our SharePoint 2013 farms. The entire SharePoint farm was not available, even the Central Administration site gave me the exact error message

image

The error is related to database. Anyway I did’t do any changes to permission or any other modifications to bring my farm down. However I thought of checking the status of database server. I navigated to DB server and SQL Server Configuration Manager.

Due to some strange reason SQL Server service was stopped. It may be due to an unplanned restart of the server.

image

image

Once I started the service, my SharePoint farm came back to life

Monday, November 4, 2013

Encrypt values of a column - SQL Server

We can use T-SQL EncryptByKey method to encrypt data in a specific column and DecryptByKey method to decrypt that. In this post I’ll explain how to use those methods.
As the first task we will create a symmetric key and a certificate as below.
USE Staff;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passwordabcd';
GO
CREATE CERTIFICATE Prod_Cert WITH SUBJECT = 'Key Protection';
GO

CREATE SYMMETRIC KEY My_Key WITH
    KEY_SOURCE = 'This is the symmetric key',
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = 'This is the symmetric key'
    ENCRYPTION BY CERTIFICATE Prod_Cert;
GO

-- Create a column in which to store the encrypted data.
ALTER TABLE dbo.Employee
ADD EncryptedAge varbinary(128); 
GO
After that we can insert/update data to the column we just created while encrypting the data.
USE Staff;
GO

OPEN SYMMETRIC KEY My_Key
   DECRYPTION BY CERTIFICATE Prod_Cert;

UPDATE dbo.Employee
SET EncryptedAge
    = EncryptByKey(Key_GUID('My_Key'), 'my new value');
GO
Values in the column are encrypted as below.

image
Use below query to decrypt the value
OPEN SYMMETRIC KEY My_Key
   DECRYPTION BY CERTIFICATE Prod_Cert;
GO

SELECT Name, EncryptedAge 
AS 'Encrypted Age',
CONVERT(varchar, DecryptByKey(EncryptedAge)) 
AS 'Decrypted Age'
FROM dbo.Employee;

GO
You can get the decrypted value as below

image