Could not register the Service Principal Name (SPN)

Recently I checked my SQL Server Error Logs. Quite some interesting information in my opinion, however I also found this message:

Date  25-7-2017 18:26:41
Log  SQL Server (Archive #3 – 25-7-2017 18:34:00)
Source  Server

Message
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SQL01.contoso.lan:NAV ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

What’s this message about?
It cleary indicates thats SQL Server couldn’t register SPN’s. I’m running SQL Server under a ‘Virtual’ account so that should be the cause of the ‘error’.

From security perspective it is recommend to run SQL Server under the least privileged account: a virtual of MSA. For more information please go to Microsoft Docs.

In order to use Kerberos authentication with SQL Server there are some conditions to be met:

– The client and server computers should be in the same domain or trusted (2 way)  domains.
– SPN’s must be registered for SQL Server

In theory I can’t connect to my SQL Server using Kerberos authentication so why I’m still able to connect to my SQL Server? What kind of authentication is being used? Even other services from other machines are still able to connect (like Microsoft Dynamics NAV). In order to get an answer you could query SQL Server. With this query you’re able to view what kind of authentication scheme is being used:

select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections

It turns out that Microsoft Dynamics NAV for example is falling back to Ntlm, intereseting… So let’s fix the SPN, restart SQL Server and look what’s happening? Now Microsoft Dynamics NAV 2017 is also connected to SQL Server but instead of Ntlm it is using Kerberos now.

In order to fix the SPN problem I manually registered the SPN in Active Directory (on the SQL Computeraccount). The errorlog states two SPN’s couldn’t be registered:

MSSQLSvc/SQL01.contoso.lan:NAV
MSSQLSvc/SQL01.contoso.lan:49753

For your information: I’m running SQL Server in a named instance called ‘NAV’ using Dynamic Ports. If you’re running SQL Server in the default instance on TCP Port 1433 the SPN’s are a little bit different. Please keep this in mind!

SQL Server Database States

In my daily job as a Technical Consultant I regularly work with  NAV and the SQL databases it’s using . I’ve seen (or set SQL) databases in the following states (in SQL Server):

Read-Only The database can be set to this mode if the data shouldn’t be updated.
Single User In single user mode only one user is allowed acces. In some situations it’s neccesary to perform maintenance tasks in Single mode.
Restricted User In restricted user mode only users with the sysadmin or dbcreator role  can access the database.
Offline Database is not available for access.
Emergency In Emergency mode you can use DBCC CHECKDB to bring the database back online again.
Recovery Pending If for example the logfile is damaged or corrupted, the database will be in this state. Action by the DBA is needed to resolve the error.

In SSMS2016 (SQL Server Management Studio 2016):

Database States in SQL 2016

Note: this is not an exhausting list of all the states. My advice is to check the File States Article on MSDN  for more information. Please also use Google for more advanced information about other database states like ‘Suspect’,’Recovery Pending’ etc.

For some states to enable I’ve used TSQL. Below you can find them:

CREATE DATABASE [Demo Database NAV (9-0) DB2]
ALTER DATABASE [Demo Database NAV (9-0) DB2] SET SINGLE_USER

CREATE DATABASE [Demo Database NAV (9-0) DB3]
ALTER DATABASE [Demo Database NAV (9-0) DB3] SET RESTRICTED_USER

CREATE DATABASE [Demo Database NAV (9-0) DB4]
ALTER DATABASE [Demo Database NAV (9-0) DB4] SET Offline

CREATE DATABASE [Demo Database NAV (9-0) DB5]
ALTER DATABASE [Demo Database NAV (9-0) DB5] SET Emergency

Move LDF, MDF or NDF file in SQL Server 2016 using Alter Database

In my previous post I explained how to move a certain database file with ATTACH/DETACH. There is another better option which I would like to share with you.:

Let’s say you need to move the NDF file. The first thing to do is to alter the database. Specify the new path of the NDF file like this:

ALTER DATABASE [NAVTest]
MODIFY FILE
(
NAME = Data
FILENAME = 'C:\NewPath\Data.ndf'
)
In order to move the NDF file you first need to take the database Offline:

use master
ALTER DATABASE [NAVTest] set offline

Now move your file and set the database online again:

use master
ALTER DATABASE [NAVTest] set Online

So in summary we have the following high level steps:

– Alter database and specify the new path of one or multiple files
– Set database Offline
– Move the physical file
– Set database Online

Now what if the path to one or more files is incorrect? You will then get a warning and the database will be in Suspect Mode.

 

 

Move LDF, MDF or NDF file in SQL Server 2016 using Attach/Detach

As a DBA or even NAV Admin this topic is very important. In my other post (Physical Database Structure of a Dynamics NAV SQL database) I explained database files and their file extensions.

Let’s say you need to move the logfile because of certain reasons like diskspace. The first thing to do is to Detach the database. This can be accomplished via SQL Server Management Studio or by using the Stored Procedure: sp_detach_db:

USE [master]
ALTER DATABASE [Demo Database NAV (10-0) RTM] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_detach_db 'Demo Database NAV (10-0) RTM'

Note: detaching a database is not like droppping a database. Dropping a database is deleting the database and their physical files: everything is gone and lost. Detaching a database only removes the ‘reference’ to your database. All your files and data are still available. After detaching your database you could move your files to another directory or disk. After moving attach your database again to bring the database online again.

Very useful queries thats show all databases and all database files:

select * from sys.databases
select * from sys.master_files

By the way: technically speaking the sys.databases is actually a view that resides in the msdb system database. Want to know more about SQL Views? Please read my post about SQL Database Objects.

So let’s create a user story: As a NAV admin you would like to move your Logfile (LDF) to another disk/directory.

High level steps:

– Inform your organization of the maintenance window (NAV will be unavailable)
– Stop all NST’s (NAV Service Tiers) that are connected to the database.
– Detach the database
– Move your logfile to another directory
– Attach the database
– Start all NST’s (NAV Service Tiers) that are connected to the database
– Dont’t forget to check if everything is working again

To attach the database please specify the new path of your LDF file:

CREATE DATABASE [Demo Database NAV (10-0) RTM] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Demo Database NAV (10-0) RTM.mdf' ),
( FILENAME = N'D:\MSSQL\Logfile\Demo Database NAV (10-0) RTM_log.ldf' )
 FOR ATTACH
GO

Now what if the path to the logfile is incorrect? You will then get an error like this:

Msg 5120, Level 16, State 101, Line 5
Unable to open the physical file “D:\MSSQL\Logfile\Demo Database NAV (10-0) RTM_log.ldf”. Operating system error 2: “2(The system cannot find the file specified.)”.
Msg 1802, Level 16, State 7, Line 5
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Obvious what to do next… please provide the correct path to the LDF. Is your LDF corrupt or want to start with a new one? Thats possible too! Like this:

CREATE DATABASE [Demo Database NAV (10-0) RTM] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Demo Database NAV (10-0).mdf' )
FOR ATTACH_REBUILD_LOG
GO

Note: this will rebuild the log in the same directory as where the MDF is created. Mission not accomplished because the logfile is still not moved to another disk. However your database is attached again and online but without the ‘original’ LDF you intended to move :)

SQL Database Objects

In this post I will tell something about Basic Database Objects in SQL like Databases, Tables, Views, Stored Procedures and Triggers.

What is a Database?
A database contains a set of tables and other objects. Each database ofcourse has a name.

What is a table?
Within a database you create tables that store information about something. For example a simple Table that stores Persons and information about them like names and their ages. For every field we need to specify what data it is going to hold (datatype). Will it, for example contain a boolean, text or integer data?

What is a view?
With a view you could obscure the real tablename for a virtual one. A big advantage is that you can define for example a WHERE clause in your view. This way when referring to this view you don’t need to specify the where clause. This makes certain operations like building or maintaining the query for your users a lot simpler!

What is a Stored Procedure and Trigger?
A stored procedure is prepared SQL code that you save. This way you can reuse it over and over again just like functions in programming languages. Triggers do the same thing except that they will be only executed when they are triggered by an event. So triggers can’t be executed manual, Stored Procedures do.

What is a Synonym?
Synonyms are a great function to obscure table names. What if a table name changes every day because of archiving reasons? With a synonym you could easily fix this problem. Synonyms are being used to redirect to a source that changes regularly like Archive Tables. This way you don’t need to hardcode the reference to the specific database.

Work smarter with SQL Server Management Studio

Do you work with the ‘Microsoft SQL Server Management Studio’ regularly. Let’s say at a weekly basis? Then please read on as I have some useful information for you which saves you time and eases your activities.
When you start the ‘Microsoft SQL Server Management Studio’ you are actually excecuting ‘C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe’ You can however add some parameters like servername, instancename, databasename and credentials. Example:

Suppress the splash screen at startup:
“C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe” -nosplash

Suppress the splash screen at startup and connect to the default instance using your current Windows Account (Windows Authentication):
“C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe” -nosplash -S . -E
Please note the little dot! The little dot represents the current local SQL Server!

Suppress the splash screen at startup and connect to the instance ‘NAV’ using your current Windows Account (Windows Authentication):
“C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe” -nosplash -S .\NAV -E

If you don’t use SQL Server Management Studio 2016 please replace 130 with the corresponding version you are using.

A list of parameters you could specify:

Microsoft SQL Server Management Studio
Usage:

ssms.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P password] [-E] [-nosplash] [file_name[, file_name]*] [-log [file_name]?] [-?]

[-S The name of the SQL Server instance to connect to]
[-d The name of the SQL Server database to connect to]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login to connect with]
[-P The password associated with the login]
[-nosplash] Suppress splash screen
[file_name[, file_name]*] Names of files to load
[-log [file_name]?] Logs SQL Server Management Studio activity to the specified file for troubleshooting
[-?] Displays this usage information

With this information you could now easily create some shortcuts to work  smarter. An example:

shortcut-target

I Hope I saved you a little bit of time and Mouse Clicks.

An update to SQL Server Management Studio is available

Recently I’ve been getting the message ‘An update to SQL Server Management Studio is available’:

SQL Server Management Studio 2016 - Update available

This message pops up in my system tray. Ofcourse great information! However the message always comes at a time I don’t have the opportunity to update. My first question was: how to control the auto update feature? The steps:

Start SQL Server Management Studio
Select ‘Check for Updates…’ from the Tools Menu:

SQL Server Management Studio 2016 - Check for Updates

The Updates window is opened. Now click ‘Details’ in order to Enable/Disable Updates, you can now see there is a checkbox ‘Automatically check for updates…’:

SQL Server Management Studio 2016 - Updates