Integrated Security = SSPI

 The term Integrated Security = SSPI is a element in a connection string for an application to get access to a data source using a OLEDB or SqlClient provider. Usually it is just used because it is recommended by someone trustworthy.

SSPI is an abbreviation to Security Support Provider Interface which is a general implementation of the Generic Security Services Application Program Interface (GSSAPI). The interesting thing is that SSPI makes it possible to get the most secure authentication in a given context. In Windows it is at least a Kerberos or NTLM authentication. When possible you will get the more secure - and faster - Kerberos authentication. This is done by a negotiation, where some parameters like rank can be configured in Windows. The Windows configuration can be through Group Policies, both local and domain.

In relation to SSPI Kerberos and NTLM are Security Support Providers (SSPs) speaking Windows lingo. The documentation from Microsoft says that you can write our own SSP, but I couldn't find many details on the subject.

Speaking about Microsoft documentation the SSPI documentation as spred across Windows Server documentation on Windows Authentication with "Security Support Provider Interface Architecture" and Windows App Development on Remote Procedure Call (RPC) with "Security Support Provider Interface (SSPI)". In general I find it somewhat difficult to find documentation on the involved components in a login and authentication process.

Using SSPI is done with different parameters and values on each driver or provider.
With the SqlClient the keyword to the ConnectionString property is "Integrated Security" and the value is "SSPI" - which is „strongly recommended“ by Microsoft. Please notice that with SqlClient 5 the namespace is changed from System.Data.SqlClient to Microsoft.Data.SqlClient as the SqlClient component is removed from the .NET framework and is now available as a NuGet package.
With the ODBC driver the keyword is "Trusted_Connection" and the value is "Yes" which is an example on the big variations on both keywords and values. If you are using a driver from another vendor than Microsoft or a driver for another environment you should be very aware on these details as they can really bite you.

 

PowerShell SMO by SqlServer module

Install SqlServer module:

Install-Module -Name sqlserver

Typical warning:

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its
InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from 'PSGallery'?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): y

See installed module:

Get-Module -Name SqlServer -ListAvailable

Import SqlServer module in PowerShell session:

Import-Module -Name SqlServer

Create SMO Server object:

$smoServer = New-Object Microsoft.SqlServer.Management.Smo.Server '(local)\SSDB2019'

See SMO Server object information:

$smoServer.Information

Top-ten posts 2023

2023 was – again – a year with far less posts than planned. Fortunately some elder posts still generate some activity on this blog.

The top-ten 2023 post list is something like Top-ten posts 2022.

Rank	Move	Rank 2022	Title	Views	Created
1	0	1	SqlBulkCopy with PowerShell	1.950	2017-07-30
2	>9	>10	High performance script timer	814	2020-01-03
3	+1	4	ISO 8601 date formatting using PowerShell	479	2008-08-14
4	>7	>10	ISO 4217 Currency list	425	2012-06-26
5	+3	8	SQL Server Agent schedule owner	360	2010-02-08
6	>5	>10	Team Foundation Server on a single server	350	2018-09-19
7	-1	6	DBCC CHECKDB with PowerShell	306	2014-10-09
8	>3	>10	Start shared HTA with PowerShell	271	2012-09-13
9	>2	>10	VLF count	247	2011-01-11
10	0	10	Audit Log for Analysis Services	204	2014-01-08

This year there are several new items on the Top-ten. My personal biggest surprise is the item on ISO 4217.
But – still my favority ISO is 8601. The oldest post is more than ten years old, which is I find both interesting and disturbing.

The posts in this blog are personal notes rather than public presentations. This is why I am fine with the low number of views.