How to Encrypt and Decrypt Text in SQL Server (2008+)

Here’s a simple example on how to encrypt (and decrypt) values in SQL Server:

How to Encrypt and Decrypt Text in SQL Server

 

--How to Encrypt and Decrypt Text in SQL Server (2008+)
--Example by Clint Huijbers

DECLARE
@Key1 NVARCHAR(50) = N'8g87g8ag4r8hinsg^%$#F&^F&^F^F&ÛIBOUG(%*^R&$%&#%^C5'
,@Key2 NVARCHAR(50) = N'&*^%R%^**F$F*OUYBYUB*F%74d654d7f685f65f56f6v6vc88d'
,@ValueToEncrypt VARCHAR(150) = 'This is the value that will be encrypted by the built-in function of SQL Server with two keys and 128-bits encryption :) blablablablablablablablablabl'
,@EncryptedValue VARBINARY(350) --Increase the VARBINARY size when you increase the lengh of @ValueToEncrypt!

SELECT @EncryptedValue = ENCRYPTBYPASSPHRASE((@Key1+N'||'+@Key2),@ValueToEncrypt)
SELECT @EncryptedValue

SELECT CONVERT(VARCHAR(150),DECRYPTBYPASSPHRASE((@Key1+N'||'+@Key2),@EncryptedValue))

 


 


 

Advertisements

Two handy queries which can help you find things in your database


--Search for a specific string in object-definitions:
SELECT DISTINCT
o.name AS Object_Name
,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%SELECT%'

--Find a column within the database:
SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Customer%'
ORDER BY SchemaName, ColumnName









SSRS Dashboarding: a ‘webdesign-look’

Couple of weeks ago, I shared my SSRS dashboard with the community on LinkedIn:

SSRS Dashboard shared on LinkedIn














Koos van Strien asked for the source (RDL) and restyled the dashboard with his ‘webdesign-look’. Pretty nice!

SSRS Dashboard response on LinkedIn

















Koos discusses the changes he made to my dashboard on his blog:
http://www.msbiblog.com/2016/01/28/ssrs-non-ugly-5-the-nitty-griddy-details/#more-402

Here are both dashboards in full size:

SSRS Dashboard Clint Huijbers

 

SSRS Dashboard Koos van Strien



 


 


 

Pause SQL Server service before restarting

Did you know that by pausing the SQL Server service before restarting the instance we allow end users to continue their work uninterrupted and we also stop any new connections to the instance? This is a nicer way of telling people to “get out” of the database in order for the server to be rebooted. I wouldn’t leave the server paused for 60 minutes of course, but I would rather use this method than forcibly disconnect users and rollback their transactions.

When a server is paused you will see messages similar to this in the SQL Server error log:

Error: 17142, Severity: 14, State: 0.
SQL Server service has been paused. No new connections will be allowed. To resume the
service, use SQL Computer Manager or the Services application in Control Panel.

Error: 18456, Severity: 14, State: 13.
Login failed for user ''. Reason: SQL Server service is paused.
No new connections can be accepted at this time. [CLIENT: ]

Next time you are worried about rebooting during the day think about the pause button instead. It might be a nice compromise for your end-users.

Thank Thomas Larock for his blog post: http://thomaslarock.com/2016/01/pause-sql-server-service-before-restarting/








SQL Server Management Data Warehouse (MDW)

The SQL Server Management Data Warehouse (MDW) is one of the most underappreciated features of SQL Server. But it’s a really handy feature which logs WaitStats and more, with almost no overhead (< 1% CPU). And…it does generate some nice reports which you could use to monitor your server (out of the box):

SQL Server 2012 Master DWH






























How to enable it
For more info (and how to enable it), please visit:
http://www.johnsansom.com/sql-server-management-data-warehouse-and-why-it-rocks/








T-SQL Metadata Queries

Stumbled upon this great post by Marco Schreuder about how to collect source system metadata with T-SQL queries:
The blog post is a few years old, but still the queries might come in handy.

TSQL Metadata Queries

The queries:

1. Get column information from all tables
2. Get column information from all views
3. Get details of foreign key constraints
4. Get details of the indices created on user tables








SQL Server Spatial Art

Yesterday I attended Hope Foley‘s webinar during 24 Hours of PASS, thanks for that!

A few examples:
Here’s Michael Coles Christmas card of 2009:
http://sqlblog.com/blogs/michael_coles/archive/2009/12/22/merry-christmas.aspx
sql-server-christmas-card-by-michael-coles

Here’s a great example by Michael J Swart:
http://michaeljswart.com/2010/02/more-images-from-the-spatial-results-tab/

sql-server-christmas-card-by-michael-j-swart

How can I create such art?
Well Alex Whittles explains how you could accomplish this on his blog:
http://www.purplefrogsystems.com/blog/2011/05/sql-server-art-using-spatial-data/

Besides that it’s cool, a more practical use for spatial data is for example if you’re in the hotel of conference business.
Imagine a report that displays your floor layout and also if a room is occupied or not? Perhaps also to display block-reservations (groups), like for example for SQL Saturday or when your local footbal club is staying at your hotel?

Here’s an example:
http://social.msdn.microsoft.com/forums/sqlserver/en-US/82706ecf-f932-417a-bb6f-b7b26a53ace6/drawingtracing-tools-for-a-blueprint

sql-server-spatial-floorplan