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

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/








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








Having ‘INSERT EXEC nested issues’?

Without manual intervention, one level of INSERT…EXECUTE is the limit

An INSERT EXEC statement cannot be nested.

It is telling you is that you can’t have a proc that does an INSERT-EXEC operation call another proc that also does an INSERT-EXEC operation.

Two basic steps to solve this:

1) Create a #Temp table or @Table var
2) OPENROWSET your stored procedure data into it


INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')



Or check out one of these links:
http://sqlserverplanet.com/sql/insert-stored-procedure-results-into-table/
http://blog.coryfoy.com/2005/07/inserting-the-results-of-a-stored-procedure-to-a-temp-table/
http://barry-king.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/
http://www.informit.com/articles/article.aspx?p=25288&seqNum=6








OPENROWSET – The object has no columns or the current user does not have permissions on that object

Today I stumbled upon this little annoying issue.
If you are trying to run a stored procedure using OPENROWSET, for example:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','EXEC [DB_Name].dbo.SP_Name')

and you’ve received the following error:

Msg 50000, Level 16, State 1, Line 231
Cannot process the object [T-SQL statement]. The OLE DB provider "SQLNCLI10" for linked server [LinkedServerName] indicates that either the object has no columns or the current user does not have permissions on that object.

The thing is that I wasn’t able to solve it properly, just found a work-around thanks to Google:

By adding ‘SET FMTONLY OFF; SET NOCOUNT ON;‘ it will ignore the metadata as it seems, it will simply return the resultset without validating:

SELECT *
FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','SET FMTONLY OFF; SET NOCOUNT ON; EXEC [DB_Name].dbo.SP_Name')



More info about it:

The OLE DB provider “SQLNCLI10” for linked server indicates that either the object has no columns or the current user does not have permissions on that object.
When one of the columns has an invallid name this can occurs. This can be fixed by altering the sys.spt_columns_view by replacing the c.name column in quotename (c.name).

To avoid this error we need to check if our SP returns a result set that has two or more columns with the same name or does not have any column name. Next we need to set NOCOUNT ON at the beginning of the procedure (just after the begin statement). If these two things are correct then the statement will execute successfully.








From DATETIME(OFFSET) to VARCHAR(x), a world of differences :)

Always use the smallest data type, but in some cases this can really bite you.
In my example, I’ve both DATETIME and DATETIMEOFFSET types in a single table.
‘Simply’ converting/casting both to strings and expecting similar results, isn’t an option.

Instead of the ‘Jan 2 2012 9:15AM’, it would be nice to just have it like ‘2012-01-02 09:15:48’
But…you’ll have to do that yourself 🙂

datetime(offset)-to-(n)varchar-print

datetime(offset)-to-(n)varchar-select

Code

PRINT CAST('2012-01-02 09:15:48' AS datetime)
PRINT CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset)
PRINT CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset)
PRINT ''
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
PRINT CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)

SELECT '1| -- '+CAST(CAST('2012-01-02 09:15:48' AS datetime) AS VARCHAR(100)) AS Results
UNION
SELECT '2| -- '+CAST(CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '3| -- '+CAST(CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset) AS VARCHAR(100))
UNION
SELECT '4| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48' AS datetime),127)
UNION
SELECT '5| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.0000000' AS datetimeoffset),127)
UNION
SELECT '6| -- '+CONVERT(VARCHAR(100),CAST('2012-01-02 09:15:48.7529001 +01:00' AS datetimeoffset),127)