Apr 29

Deploying Dacpac with Octopus

Here’s a full deployment script for deploying dacpac with Octopus to SQL 2014

# Set params

$databaseName = “AdventureWorksDW”

# Add the DLL

# For 32-bit machines

#Add-Type -path “C:\Program Files\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll”

# For 64-bit machines

Add-Type -path “C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll”

# Create the connection strnig

$d = New-Object Microsoft.SqlServer.Dac.DacServices “server=(local)”

#Set Package version such as $PackageVersion = “”

$PackageVersion = $OctopusParameters[‘Octopus.Action[Push nuget package to the server].Package.NuGetPackageVersion’]

#Set Application Directory, such as “C:\Octopus\Applications\Test\AdventureWorksDW\”

$ApplicationPath = $OctopusParameters[‘Octopus.Tentacle.Agent.ApplicationDirectoryPath’]

#Set Environment Name

$EnvName = $OctopusParameters[‘Octopus.Environment.Name’]

#Set PackageName

$PackageName = $databaseName

$dacpac = $ApplicationPath + “\” + $EnvName + “\” + $PackageName + “\” + $PackageVersion + “\” + $databaseName + “.dacpac”

Write-Host $dacpac

# Load dacpac from file & deploy to database

$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)

# Set the DacDeployOptions

$options = New-Object Microsoft.SqlServer.Dac.DacDeployOptions -Property @{

‘BlockOnPossibleDataLoss’ = $true;

‘DropObjectsNotInSource’ = $false;

‘ScriptDatabaseOptions’ = $true;

‘IgnorePermissions’ = $true;

‘IgnoreRoleMembership’ = $true


# Generate the deplopyment script

$deployScriptName = $databaseName + “.sql”

$deployScript = $d.GenerateDeployScript($dp, $databaseName, $options)

# Return the script to the log

Write-Host $deployScript

# Write the script out to a file

$deployScript | Out-File $deployScriptName

# Deploy the dacpac

$d.Deploy($dp, $databaseName, $true, $options)

Apr 14

Service Principal Name (SPN): how to get it right?

Most of the difficulties in configuring Kerberos actually stem from this absolutely fundamental step in the process: getting your SPNs right. target They help Kerberos find the Service and the computer (Principal) that hosts that service. That is, SPNs are all about the target. They identify the destination of your journey, much like the address for the mail. In the case of Kerberos delegation, they are not about the client, not about the delegating service, but the service that serves the data you are trying to access. OK, there are exceptions, of course: most notably when you would like to use Kerberos to connect to the front end of your application (e.g. SharePoint Portal), but they only prove the rule, as in this case SharePoint portal is the target of your journey!

Now the second important concept to grasp is the syntax of the command commonly used to set up SPNs: SETSPN. Inline help is very generic and does not go very far in helping you with the syntax, but it tells you two very important things – you need to register your SPNs against an account and you need to identify the service:

setspn.exe -S <SPN> <AccountName>

Note here the use of the -S flag. If your domain is at 2003 functional level or later, this is the flag to use: it automatically checks for any duplicate SPNs before adding another. This is a very important step: you do not what to confuse your Kerberos about the destination of your journey! In pre-2003 domains have to check for duplicates explicitely and add SPNs with -A flag.

So how do we udentify the service? This is the hardest bit: there are thee components to this:

  1. Service class or description of the service
  2. Service Host name or DNS “A” record (not CNAMEs!)
  3. Port or Instance Name

Service class is merely an arbitrary label describing the service, naturally it should be commonly recognised, such as HTTP for a web site (IIS Service). Here’s some of the examples of service classes:

Service Classes (Types) Service Classes Description
MSSQLSvc SQL Server Database Engine
MSOLAPSvc.3 SQL Server Analysis Services
MSOLAPDisco SQL Server Analysis Services Discovery Service
MSSQLDisco SQL Server Browser
HTTP IIS service

The second part is the Host name: that is definition of where the service is running. This is normally a NetBIOS name of the computer that is hosting that target service, however in the case of IIS service, that should me the DNS “A” record of the Service your are authenticating to. Please bear in ming 15 character limit over Microsoft implementation of the NetBIOS: make sure your server naming conventions do not exceed this limit. NetBIOS name is one of the properties of your computer account in active directory. Easiest way to check NetBIOS name is is to display Computer Name tab of the host computer properties (right click on My Computer->Properties) and then click “Change…” -> “More…”. Checking DNS records is a little more involved, but your domain admin will be able to help.

The third part only applies to services running on non-default ports and named instances of Analysis Services. Naturally, you need to know if it is indeed the case. Table below should help you make a call:

Service Class Default Port How to tell if the service is set to default
SQL Server Database Engine 1433 Default instance is usually running on default port. Named instances usually set to dynamic allocation, check SQl Server Configuration manager
SQL Server Analysis Services 2383 If Port property of Analysis Services instance in Management Studio has a default value of 0, this means that the instance uses the default port, 2383
SSAS Discovery Service 2384 Usually Default ports are used. Check what ports are used by sqlbrowser.exe using “netstat -aob” command
SQL Server Browser 1434 Usually Default ports are used. Check what ports are used by sqlbrowser.exe using “netstat -aob” command
IIS service 80 Check web site properties in IIS Manager. If port is not specified as a part of URL for the web site, then it is usually default.

Now you should be able to form a correct SPN using the following syntax:

<service class>/<Host Name>[:<port or instance>]

Please remember to use instance name for the named instances of Analysis Services. If you try to specify a port number instead, it will be treated as a part of the host name and of course the call will fail miserably.

Finally, one needs to register this beautifully crafted SPN against an account. This account is the identity of the service you are trying to reach – the identity of the target service, the account that runs that service. Ideally, this is an Active Directory Service Account specially configure to be used as a service identity. If the service is running as “Local System” then this is Active Directory account of the computer that runs the service. Either way, in the SETSPN command it has to follow correctly formed SPN using the following syntax:

<domain name>\<account name>

 Thus a complete syntax of a correct SPN statement should be as follows:

setspn -s <service class>/<Host Name>[:<port or instance>] <domain name>\<account name>

To make it completely fool-proof, I have set up a very straightforward spreadsheet that you can download and use for building a perfect SPN statement every time. Please refer to KerberosHelper post to download.

Mar 30

I am presenting at SQL Bits!

SQLBitsLogoSaying I am excited to present at SQL Bits is an understatement. I always enjoy sharing what I know with others, but now I have a perfect chance of sharing what I’ve learned about Kerberos delegation with UK SQL Server Community and the entire world! Click on the part-eaten disk array image to find out more…