Insufficient access rights to perform the operation when running setspn

When attempting to add an SPN to a service account for SQL Server, you may get the following error if you are not a domain admin:

setspn -S MSSQLSvc/VSQLDEV01.DOMAIN DOMAIN\SVCACCOUNT.SVC
Checking domain DC=..,DC=....,DC=..,DC=..

Registering ServicePrincipalNames for CN=vsqldev01 svc,OU=Service Accounts,OU=Shared Resources,OU=..,DC=..,DC=
...,DC=..,DC=..
 MSSQLSvc/VSQLDEV01.DOMAIN
Failed to assign SPN on account 'CN=vsqldev01 svc,OU=Service Accounts,OU=Shared Resources,OU=E..,DC=.,DC=...
,DC=..,DC=..', 
error 0x2098/8344 
-> Insufficient access rights to perform the operation.

If your lucky enough, then get your domain admin to give you the required permissinos against the OU in Active Directory. They would need to do the following:

On a Domain Controller, run adsiedit.msc (Doing this via the normal dsa.msc console will not expose the spn permissions that need to be added)

Then run the following sequence of actions:

Right-Click on the OU and select Properties
Select the "Security" tab
Select the "Advanced" tab
 Select the "Add" button
 Enter the security principal name
 security principal
  Ok
 Properties tab
 Apply to:
 Descendant User objects
 Permissions:
 Read servicePrincipalName - Allow
 Write servicePrincipalName - Allow
  Ok
 Ok
Ok

 

Advertisements

Set up Windows Auth to a SQL Instance with SQL Ops Studio on Mac OS

Microsoft released a preview of SQL Ops Studio in the last week or two, and as a Mac user I was interested to see how well the interface would work compared to SSMS.

Here is a quick intro to the produce from Microsoft: https://www.youtube.com/watch?v=s5DopE7ktwo
More details about Ops Studio can be found here: https://docs.microsoft.com/en-us/sql/sql-operations-studio/what-is
Download – https://docs.microsoft.com/en-us/sql/sql-operations-studio/download

For reference, I am using MacOS Sierra version 10.12.6 and this is what I did to get windows authentication working properly.

If you try to get Windows AD Auth working, you might initially see this error:

dbamohsin-opsstudio-connectionerror

The link in the message above takes you to: Connect SQL Operations Studio (preview) to your SQL Server using Windows authentication – Kerberos

There are 3 main areas to configure before Windows Auth to a SQL Instance works.

Service Principal Names (SPN)

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.

As an Example. on a SQL Clustered instance called VSQLDEV01, you can check if the SPN is set by running the following in a command prompt/ps terminal:

setspn -L VSQLDEV01

If it doesnt return a row that contains the following:

MSSQLSvc/VSQLDEV01.<DOMAIN.COMPANY.COM>

MSSQLSvc/FQDN:port | MSSQLSvc/FQDN, where:

  • MSSQLSvc is the service that is being registered.
  • FQDN is the fully qualified domain name of the server.
  • port is the TCP port number.

You can add an SPN to register the service account by doing the following:

setspn -A MSSQLSvc/VSQLDEV01.DOMAIN.COMPANY.COM DOMAIN\SQLSERVICEACC

Get Key Distribution Center (KDC) and join Domain

The KDC is usually just the FQDN of your Domain Controller – fairly straightforward to find out via the nltest command on a windows machine:

 nltest /dsgetdc:DOMAIN.COMPANY.COM
Configure KDC in krb5.conf on your mac

Edit the /etc/krb5.conf in an editor of your choice. Configure the following keys

sudo vi /etc/krb5.conf

[libdefaults]
  default_realm = DOMAIN.COMPANY.COM

[realms]
DOMAIN.COMPANY.COM = {
   kdc = dc-33.domain.company.com
}

Then save the krb5.conf file and exit

Test Granting and Retreiving a ticket from the KDC

On your Mac, run the following in a terminal:

kinit username@DOMAIN.COMPANY.COM

Authenticate and then check the ticket has been granted:

Credentials cache: API:9999E999-99CA-9999-AC9C-99A999999D99
        Principal: ADUSER@DOMAIN.COMPANY.COM
Issued                Expires               Principal
Mar  8 07:55:10 2018  Mar  8 17:55:01 2018  krbtgt/DOMAIN@DOMAIN

Hopefully, if the intruction are followed, then you should be ready to go!

Make a connection using Windows Auth via SQL Ops Studio.

To test a connection has authenticated via KERBEROS, you can check in sql once a connection is made:

SELECT 
session_ID, 
connect_time, 
net_transport, 
protocol_type, 
auth_scheme 
FROM sys.dm_exec_connections
WHERE auth_scheme = 'KERBEROS'

Should return your connected session:

Auth_scheme

Check compressed database backup ratio

If running compressed backups natively in SQL Server, then you can check teh ratio of compressed using the following script:

SELECT backup_size/compressed_backup_size 'Ratio', backup_size/1024/1024/1024 'Original Size GB', compressed_backup_size/1024/1024/1024 'Compressed Size GB', 
backup_start_date, backup_finish_date FROM msdb..backupset
where database_name = 'Database_Name'
and backup_size/compressed_backup_size > 1
order by backup_start_date desc;

Setting up SQL Server vNext CTP 2.0 on Docker

Recently I moved into the brave new world of having a Macbook pro as my main laptop, and one of my first frustrations was the inability to be able to run SQL Server Management Studio locally. Unfortunately Microsoft haven’t come up with a solution for that one, so I’m having to do with a mix of SQLPro Studio and running SSMS inside a VDI machine on the mac.

One thing the Mac has made easier for me is the ability to start testing SQL Server vNext locally and without too much messing around. So when Microsoft released vNext 2.0 on the 19th April 2017, it seemed like a good opportunity to give it a try. (Whats new in SQL Server Linux)

This post should cover what you need to do to get a docker image of SQL Server running on macOS Sierra. The Microsoft documentation is pretty good on this subject, and not convoluted but I always find it good to blog how exactly I’ve done things, as there are always little quirks.

Install Docker for Mac

I downloaded the stable version just to be a bit safer and so that I wouldn’t introduce any unnecessary pinch points.

https://docs.docker.com/docker-for-mac/install/

Once installed, Start the Docker application – it may take a few minutes to initialise, but eventually you will end up with the docker ship icon in the top bar.

There are a few minimum requirements for running docker for the SQL Server image:

  • Docker Engine 1.8+ on any supported Linux distribution or Docker for Mac/Windows.
  • Minimum of 4 GB of disk space
  • Minimum of 4 GB of RAM

If your downloading a recent release of docker then the engine shouldn’t be an issue.

At the time of writing, my version is:

$ docker version
Client:Version	17.03.1-ce
API Version: 1.27
Go Version: go1.7.5

Server:
Version: 17.03.1-ce
API Version: 1.27 (minimum version 1.12)
OS/Arch: linux/amd64

Docker default starts with 2GB of RAM, but this can be easily changed to 4GB from the docker preferences in the advanced section.

Pull down and run the latest SQL Server Docker Image – Step by Step details here – https://docs.microsoft.com/en-gb/sql/linux/sql-server-linux-setup-docker

Quick Steps here:

sudo docker pull microsoft/mssql-server-linux

Note that I have added a –name switch to the docker command below. This is to simplify things once we have everything up and running as the container name is quite critical for more or less every command in docker.

sudo docker run --name SQL2017 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=' -p 1433:1433 -d microsoft/mssql-server-linux

To make sure we have successfully created our container, we can run:

docker ps -a

To familiarise yourself with other docker commands, you can run:

docker --help

Connect to the SQL Server

sudo docker exec -it SQL2017 /bin/bash

The flags are for the following:

-i, –interactive Keep STDIN open even if not attached–privileged Give extended privileges to the command-t, –tty Allocate a pseudo-TTY

This then takes you into the interactive command line for the docker image.

The folder structure by default is /var/opt/mssql for the database files & error logs. Sqlcmd is in /opt/mssql-tools/bin/

Reading the error log

While in exec mode for the SQL2017 container, run the following cat command:

cat /var/opt/mssql/errorlog

There is a very good guide by microsoft on how to troubleshoot SQL Server Linux which can be found here – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide

Using sqlcmd

Starting with SQL Server vNext CTP 2.0, the SQL Server command-line tools are included in the Docker image. If you attach to the image with an interactive command-prompt (as I have done above), you can run the tools locally.

First step is to add the sqlcmd path to the $PATH environment variable. This step isn’t critical but makes sqlcmd accessible from any location on the docker image

PATH=$PATH:/opt/mssql-tools/binecho 'export PATH="$PATH:/opt/mssql-tools/bin"'
~/.bash_profile&nbsp;
root@5214e1df3c86:/opt/mssql-tools/bin# echo $PATH
/opt/mssql-tools/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
sqlcmd -S . -U sa -P
1&gt; select name from sys.databases
2&gt; go
name
-----------------------------------------------------------
master
tempdb
model
msdb

Installing SQLCMD on macOS

/usr/bin/ruby -e "$(curl -fsSL <a href="https://raw.githubusercontent.com/Homebrew/install/master/install" data-mce-href="https://raw.githubusercontent.com/Homebrew/install/master/install">https://raw.githubusercontent.com/Homebrew/install/master/install</a>)"
brew tap microsoft/mssql-preview <a href="https://github.com/Microsoft/homebrew-mssql-preview" data-mce-href="https://github.com/Microsoft/homebrew-mssql-preview">https://github.com/Microsoft/homebrew-mssql-preview</a> 
brew update 
brew install mssql-tools #for silent install ACCEPT_EULA=y 
brew install mssql-tools

 

Can be quickly tested locally by opening a terminal window and running:

sqlcmd -S my-remote-server -U test -P test
1&gt; select @@SERVERNAME
2&gt; go
-----------------------------------------------------------------
SQLSERVER/MYNAMEDINSTANCE
(1 rows affected)

Connecting into the docker image from host machine

Get the IP of the container

MCR-AL33450:/mohsin.alipatel$ docker inspect --format "{{ .NetworkSettings.Ports}}" SQL2017
map[1433/tcp:[{0.0.0.0 1433}]]

Then make a sqlcmd connection using the IP and port above

MCR-AL33450:/mohsin.alipatel$ sqlcmd -S 0.0.0.0,1433 -U sa -P
1&gt;Select @@servername
2&gt;go-----------------------------------------------------------------------------------------------
5212e1df3c86
(1 rows affected)

This should provide a basic introduction to both docker and to SQL server on linux.

Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##"

Following error continuously in the agent log after restoring the msdb database

The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following: 'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

This is normally down to an orphaned login for the ‘##MS_PolicyEventProcessingLogin##’ login

To fix, you can first make sure if an orphaned login is in fact the cause:

USE master
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go
USE msdb
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go

This should show that the SID for the login in the master database is different from the SID in the msdb database

image

Then run the following to correct the orphaned login:

use [master]
go
exec sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##';
go
use [msdb]
go
exec sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##';
go

By rerunning the check, it should show that the SID is now the same in both database_principals tables

USE master
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go
USE msdb
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go

image

To clean up, cycle the agent error log

use msdb
go
sp_cycle_agent_errorlog