Setting up powershell remote sessions

Hey guys, differing from usual this is a quick post on setting up powershell remote sessions. I know you can remotely connect to powershell sessions using the Server Manager that comes with Windows Remote Administration Tools but it’s a bit of a clicky process and I like to eliminate using the mouse as much as possible.

Disclaimer! I’m not a scripter, there are probably much better ways of doing this but I’ll show you the way I set it up and how to fix any errors you may come across.

So here goes, first thing to do is check that the Windows Remote Management service (WinRM) is running, so open a powershell session (as an admin) and run:-

Get-Service WinRM

If it’s not running, start the service:-

Start-Service WinRM

And now enable powershell remoting:-

Enable-PSRemoting -force

Next thing to do is check your trusted hosts lists, so run:-

cd wsman::localhost
cd Client
dir

N.B. – I’m going to the Client via two cd commands as going straight to wsman::localhost\Client sometimes generates an error (not sure why TBH). If you get errors, try each command separately.

If the host you want to remote to isn’t in the list, run the following (changing the RemoteComputer1,RemoteComputer2 to your servers): –

winrm s winrm/config/client '@{TrustedHosts="RemoteComputer1,RemoteComputer2"}'

Btw, if you see this error:-
WinRM Error

Restart the WinRM service: –

Stop-Service WinRm
Start-Service WinRm

Cool, so now we can connect to a remote powershell session on the servers entered into the list. Save this simple script somewhere: –

Param (
    [string]$Server,
    [string]$Credential
      )

Enter-PSSession -ComputerName $Server -Credential $Credential

Which can be called by a .BAT file, passing in the parameters:-

powershell -NoExit -ExecutionPolicy Bypass -File 'PATH TO THE PS1 File' -Server YOURSERVER -Credential YOURLOGIN

N.B. – Use the -Credential parameter if your server is in a different domain (like a Staging or QA environment).

This .BAT file can be called via a cool program like Launchy and voila, a remote powershell session!

SQL 2016 – Dynamic Data Masking

One of the new features that’s coming with SQL Server 2016 is Dynamic Data Masking. DDM is designed to allow DBAs to limit exposure of sensitive data within a database with minimal (if any) changes required to existing queries.

How often have you seen a column in a database…in plain text…called password?? ARGH.

I really like the idea of being able to mask data without having to change any existing queries in the database. But how does it work? Well, SQL doesn’t do anything to the data itself but when a select statement retrieves data a “masking” function is applied to the columns (I won’t blame you if you just rolled your eyes and said “well…duh” when reading that last statement). These functions can be:-

Default: Changes column values to xxxx

MASKED WITH (FUNCTION = 'default()'))

Email: Changes email address to XXXX@XXXX.com

MASKED WITH (FUNCTION = 'email()')

Random: Replaces numeric data with range of values specified. Example shown will replace data with values from 1 to 100.

MASKED WITH (FUNCTION = 'random(1,100)')

Custom: A user defined type that can mask characters specified. Example shown masks all characters apart from the first and last with XXX.

MASKED WITH (FUNCTION = 'partial(1,"XXX",1)')

These functions can be applied when the table is created or added afterwards by running a ALTER TABLE statement. But enough of me explaining, let’s create a database for testing: –

USE [master];
GO

CREATE DATABASE [DDM_Demo];
GO

Now we can create a table, specifying the masking function inline: –

USE [DDM_Demo];
GO

CREATE TABLE dbo.Users
(UserID			INT IDENTITY(1,1) PRIMARY KEY,
 Forename		VARCHAR(20),
 Surname		VARCHAR(20),
 Username		VARCHAR(20),
 Email			VARCHAR(50) MASKED WITH (FUNCTION = 'email()'),
 AccountPassword	VARCHAR(50) MASKED WITH (FUNCTION = 'default()'));
GO

This will create a pretty simple table with the columns Email and AccountPassword masked with the email and default functions respectively. To see how they work insert some test data: –

INSERT INTO dbo.Users
(Forename,Surname,Username,Email,AccountPassword)
VALUES
('Andrew','Pruski','APruski','dbafromthecold@gmail.com','123456ABCDE');
GO

But I’m guessing your a sysadmin in your test instance (if you’re not, what’s going on there?) so in order to see the data masking in action, we need a new login & user: –

CREATE LOGIN [DDM_Login] WITH PASSWORD = 'testing', CHECK_POLICY=OFF,CHECK_EXPIRATION=OFF;
CREATE USER [DDM_User] FOR LOGIN [DDM_Login];
ALTER ROLE [db_datareader] ADD MEMBER [DDM_User];
GO

Now run a SELECT statement with under the new account’s credentials: –

EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

ddm1

Cool! The data has been masked to the user. If we want this user to be able to see the data, we can run: –

GRANT UNMASK TO DDM_User;
GO

Now re-run the SELECT statement: –

EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users;
REVERT;
GO

ddm2

And now the user can see the data. To remove the access: –

REVOKE UNMASK TO [DDM_User];
GO

To add a new column with masking (this time using the custom function): –

ALTER TABLE dbo.Users
ALTER COLUMN Surname ADD MASKED WITH (FUNCTION = 'partial(1,"XXX",1)');
GO
EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

ddm3

The column has now been masked with a custom function. To remove the data masking function from the column, run: –

ALTER TABLE dbo.Users
ALTER COLUMN Surname DROP MASKED;
GO

N.B. – As far as I can tell, adding/dropping the data masking function is a meta-data only operation (if anyone knows otherwise, let me know!).

Obviously there are some limitations around columns that can have masking applied. Masking cannot be applied to: –

  • Columns already encrypted
  • Filestream
  • Sparse columns
  • Computed columns

There is a caveat with computed columns however, if a computed column depends on a masked column then the computed column WILL be masked. Also, masked columns cannot be part of a key for a full text index.

A final word of caution with this feature…THIS IS NOT ENCRYPTION! It is exactly what it says it is, a mask. This can be seen by having a look at the data on the page: –

DBCC IND('DDM_Demo','Users',1);
GO

DBCC TRACEON(3604);
GO
DBCC PAGE ('DDM_Demo',1,232,3);
GO

N.B. – For more info on DBCC IND & DBCC PAGE go here:- http://strictlysql.blogspot.ie/2010/08/dbcc-ind-dbcc-page-intro.html

ddm4

The data can be seen in plain text on disk, the masking function only comes into play when reading the data. I’ve been playing around with this and, I can’t see any huge performance impact incurred when having to mask the data. SQL does report a extra operator when reading the data: –

GRANT SHOWPLAN TO DDM_User;
GO

EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

ddm5

That COMPUTE SCALAR operator isn’t there usually when running a bog standard select against a table but I’ve tried inserting 1,000,000 rows into the table, selecting from it, then dropping the masking, re-running the select and comparing the performance statistics but cannot see any (significant) impact. Here’s the code I used to test (have a go at running it yourself): –

--Switching off performance metrics for the inserts
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SET NOCOUNT ON;
GO

--Inserting a load of rows to the table
--I know this is horrible code!!!!!!!!!!!!!!!!!!!!!!!!!
DECLARE @Counter INT = 0;

WHILE @Counter <= 1000000
BEGIN

    INSERT INTO dbo.Users
    (Forename,Surname,Username,Email,AccountPassword)
    VALUES
    ('TestForename' + CONVERT(SYSNAME,@Counter),
     'TestSurname'  + CONVERT(SYSNAME,@Counter),
     'TestUsername' + CONVERT(SYSNAME,@Counter),
	'testemail'    + CONVERT(SYSNAME,@Counter) + '@gmail.com',
	'TestPassword' + CONVERT(SYSNAME,@Counter));
    
    SET @Counter = @Counter + 1
END


--Allowing the user to see the execution plan
GRANT SHOWPLAN TO DDM_User;
GO

--Cleaning out the data from the buffer pool
DBCC DROPCLEANBUFFERS;
GO

--Running the initial select
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

--Dropping the masking
ALTER TABLE dbo.Users
ALTER COLUMN Email DROP MASKED
ALTER TABLE dbo.Users
ALTER COLUMN AccountPassword DROP MASKED;
GO

--Cleaning out the data from the buffer pool (again)
DBCC DROPCLEANBUFFERS;
GO

--Re-running the SELECT without the masking
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

I have seen a minor increase in execution times but nothing that makes me worried. I am aware that this is a very simple test so any “gremlins” may not be apparent so as ever with a new feature, if you’re going to implement it, get it on a test system and hammer the thing!

Thanks for reading.

Tools of the trade

I’ve been meaning to write this post for a while now, nice and simple, just want to list the tools that I use on a day-to-day basis and hopefully a couple of them may be new to someone out there. Btw – if anyone uses anything else regularly and thinks I should be, let me know!

So first off has to be SQL Server Management Studio. There are other applications out there that can be used to administer/query SQL Server but I’m yet to find one that beats SSMS. Sure, it has it’s problems but for ease of use and the sheer amount of plugins available, I can’t see myself ever using anything else (thankfully it’s now available separately from SQL Server itself, how long did it take Microsoft to do that??).

Here’s the plugins that I have (they’re all free ‘cos I’m cheap): –

SQLSentry Plan Explorer
Everyone should have this installed, if you don’t…go install it now. Seriously, do it now.

RedGate SQL Search
Great tool for finding objects in your SQL instances. Don’t use it that much but it’s awesome to have already there when needed.

SSMSBoost
To be honest, I only ever use the the option to format SQL on a regular basis and if you only want that you could be better off downloading the Poor Man’s T-SQL Formatter. However the option to restore previously closed tabs has been a lifesaver in the past.

Here’s some of the other tools I use on a daily basis (again all free, did I mention that I’m cheap?): –

Edit – BeyondCompare isn’t free, you have a 30 day free trial and then it’s $30 (well worth it)

Terminals
Good RDP tool, I admit that I haven’t really tried any others as this does exactly what I need

Notepad++
I don’t think I need to say anything about this. I’ve tried other text editors (atom is pretty cool) but nothing imho comes close to this.

Launchy
I love this tool. I really love this tool. If you’re not using it, go and download it now. Yes it’s an application launcher it’s also much more. I use it as a app launcher but also to search for files/folders and run .bat scripts that perform maintenance tasks on my laptop. Very cool, go and download it.

QDir
For moving files around, I can’t recommend this enough. Loads of options, easy to use and navigate. There’s hundreds of multi-pane file explorers out there but I’ve found that this one has the best user experience (not that I’ve tried all of them mind).

BeyondCompare
If you have to compare scripts, this is a life saver. I often have to review changes being made to stored procedures this tool easily allows me to see the differences between the existing and new code. This has saved me hundreds of hours (not exaggerating).

Thanks for reading!