MSSQL Server – Tim van Kooten Niekerk : /DEVOPS

MSSQL Query Value From XML Datatype

2018-06-27 / MSSQL Server / Tim van Kooten Niekerk

Query some values from a XML datatype result…

SELECT [datumtijd]
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/"; 
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:ID'
                               ).value('.', 'nvarchar(100)')) AS ID
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/";
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:Name'
                               ).value('.', 'nvarchar(100)')) AS Name

      --,[body]
FROM [DATABASE].[dbo].[Logtable] 
WHERE [kolom1] = 'waarde' 
ORDER BY datumtijd DESC

MSSQL Test Connection String With PowerShell

2016-10-27 / MSSQL Server / Tim van Kooten Niekerk

Quickly test a connection string with PowerShell.

$sConString = "Data Source=localhost;Integrated Security=true;Initial Catalog=master; MultipleActiveResultSets=True;" 
$oSQLCon = new-object ("Data.SqlClient.SqlConnection") $sConString 
$oSQLCon.Open() 
$oSQLCon.Close()

MSSQL Check Effective Permissions

2016-02-26 / MSSQL Server / Tim van Kooten Niekerk

Check effective permissions on al securable using sys.fn_my_permissions(‘securable’, ‘class’). Class can be OBJECT, ROLE, SCHEMA, USER, etc.

EXECUTE AS LOGIN = 'DOMAIN\User'
SELECT * FROM sys.fn_my_permissions('dbo.TableName', 'Object')
REVERT

Create server or database role (QuickRef).

CREATE (SERVER) ROLE rolename

ALTER (SERVER) ROLE rolename ADD MEMBER [DOMAIN\User]

Grant or revoke permissions (QuickRef).

>GRANT SELECT, INSERT ON [dbo].[ViewName] TO DOMAIN\User
>GRANT EXECUTE ON [dbo].[Procedure] TO [RoleName]

MSSQL Query SQL Trace File

2016-02-25 / MSSQL Server / Tim van Kooten Niekerk

Query data from a SQL trace file directly.

SELECT TextData, StartTime, Duration
FROM fn_trace_gettable('X:\Path\To\TraceFile.trc', default)
WHERE EventClass = 41;

For a complete list of eventclasses you can use the query below.

SELECT * FROM sys.trace_events

MSSQL Create Update Trigger (QuickRef)

2016-02-25 / MSSQL Server / Tim van Kooten Niekerk

A basic update trigger. This specific trigger can be used for audit purposes.

CREATE TRIGGER dbo.TriggerDesc_UPDATE
ON dbo.TableName FOR UPDATE
AS
BEGIN
  IF UPDATE(ColumnName)
  BEGIN
    INSERT INTO dbo.AuditLogTable (IDColumn, OldValue, NewValue)
    SELECT i.IDColumn, d.Value, i.Value
    FROM inserted I
    JOIN deleted d ON i.IDColumn = d.IDColumn
  END
END

MSSQL Query Session Information

2016-02-25 / MSSQL Server / Tim van Kooten Niekerk

Get info on non-sleeping sessions or open open transactions.

SELECT DB_NAME(database_id) AS dbname, *
FROM sys.dm_exec_sessions WHERE 1=1
AND is_user_process > 0
AND ( open_transaction_count > 0 OR status <> 'sleeping' )
ORDER BY database_id

MSSQL Bulk Import / Export

2016-02-23 / MSSQL Server / Tim van Kooten Niekerk

Create a format file using BCP command line tool.

bcp DatabaseName.dbo.TableName format nul -S ServerName -T -c -t ',' -r '\n' -x -f X:\Path\To\FormatFile.xml

Export a table to a CSV-File using a format file (BCP).

bcp DatabaseName.dbo.TableName out X:\Path\To\ExportFile.csv -S ServerName -T -f X:\Path\To\FormatFile.xml

Insert data from a CSV-File using a format file (OPENROWSET).

INSERT INTO dbo.TableName
SELECT * FROM OPENROWSET (BULK X:\Path\To\ExportFile.csv', FORMATFILE = X:\Path\To\FormatFile.xml') AS rows
WHERE IDColumn < 200;

Insert data from a CSV-File (BULK INSERT).

BULK INSERT dbo.TableName
FROM 'X:\Path\To\ExportFile.csv'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR ='\n');

MSSQL Configure Database Encryption

2016-02-23 / MSSQL Server / Tim van Kooten Niekerk

Configure database encryption and backup keys en certificates. This manual shows the complete chain from Service Master Key to the Database Encryption Key. Please remember to at least backup the Server Certificate (marked: “[*** IMPORTANT ***]”) and store these files in a secure location. Without this certificate you cannot access your data when it’s moved or restored to another server. You should substitute all (random generated) passwords with your own.

STEP 1 – Backup and restore (for verification purposes) Service Master Key.

USE master;
GO

-- Backup Service Master Key...
BACKUP SERVICE MASTER KEY TO FILE = 'X:\Path\To\ServiceMaster.key' 
ENCRYPTION BY PASSWORD = 'ooxuRai4shaid0AvnieLoh6t'

-- Restore Service Master Key...
RESTORE SERVICE MASTER KEY FROM FILE = 'X:\Path\To\ServiceMaster.key' 
DECRYPTION BY PASSWORD = 'ooxuRai4shaid0AvnieLoh6t' FORCE

STEP 2 – Create, backup and restore (for verification purposes) Database Master Key and Server Certificate.

USE master;
GO

-- Create Database Master Key...
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah';
GO

-- Backup Database Master Key...
BACKUP MASTER KEY TO FILE = 'X:\Path\To\Master.key'
ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO

-- Drop Database Master Key for restore test...
DROP MASTER KEY
GO

-- Restore Database Master Key...
RESTORE MASTER KEY FROM FILE = 'X:\Path\To\Master.key' 
DECRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO

-- Open key and let Service Master Key decrypt (for transparent encryption)...
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

-- Create Server Certificate...
CREATE CERTIFICATE ServerCertDefault WITH SUBJECT = 'Server Certificate Default'
GO

-- Backup Server Certificate and Key [*** IMPORTANT ***]...
BACKUP CERTIFICATE ServerCertDefault TO FILE = 'X:\Path\To\ServerCertDefault.cer'
WITH PRIVATE KEY ( FILE = 'X:\Path\To\ServerCertDefault.key', 
ENCRYPTION BY PASSWORD = 'Eeh3aeb0EiTh2ohZneil8ueS' );
GO

-- Remove Server Certificate for restore test....
DROP CERTIFICATE ServerCertDefault
GO

-- Restore Server Certificate with Key...
CREATE CERTIFICATE ServerCertDefault 
FROM FILE = 'X:\Path\To\ServerCertDefault.cer'
WITH PRIVATE KEY ( FILE = 'X:\Path\To\ServerCertDefault.key' , 
DECRYPTION BY PASSWORD = 'Eeh3aeb0EiTh2ohZneil8ueS' );
GO

STEP 3 – Turn on database encryption in database.

USE [Database]
GO

-- Create Database Encryption Key...
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertDefault
GO

-- Enable Encryption...
ALTER DATABASE [Database] SET ENCRYPTION ON
GO

-- Check if database encryption is turned on...
SELECT name, is_encrypted FROM sys.databases WHERE is_encrypted = 1

Results:
name                                                           is_encrypted
-------------------------------------------------------------- ------------
DatabaseName                                                   1

MSSQL master Database Maintenance

2016-02-23 / MSSQL Server / Tim van Kooten Niekerk

Command line options to start SQL server in single user mode for master database restore. Add -f for a minimal setup.

sqlservr.exe -c -m –sINSTANCENAME

Setup option to recreate the master database.

./setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DOMAIN\adminaccount

MSSQL Enable/Disable Indexes and Constraints (QuickRef)

2016-02-23 / MSSQL Server / Tim van Kooten Niekerk

Enable or disable a foreign key constraint.

ALTER TABLE dbo.TableName [NO]CHECK 
CONSTRAINT [FK_TableName_FTableName_IDColumn|ALL];

Enable or disable an index.

ALTER INDEX [IX_TableName_IndexName|ALL] 
ON dbo.TableName [DISABLE|REBUILD];

Recreate an index

CREATE INDEX IX_TableName_IndexName ON dbo.TableName(ColumnName) 
WITH ( DROP_EXISTING = ON );