Create SQL Ad Hoc Distributed Queries and SQL Server Encrypted Stored Procedure
In this SQL tutorial, I want to give SQL Ad Hoc Distributed Queries examples with SQL Server encrypted stored procedures.
Why sql developers might prefer to use t-sql encrypted stored procedure with ad hoc distributed queries like t-sql OpenRowSet queries ?
The reason for choosing encryption on sql ad hoc queries is you can hide the sql username and password for sql authentication methods.
For example, if you have to set explicity the sql user name and the password of the sql user in your sql OpenRowSet query, by encrypting stored procedure on SQL Server the authentication information will be kept away from the eyes of unwanted people.
Create T-SQL OpenRowSet SQL Ad Hoc Distributed Query Example
SQL developers will find in this sql tutorial how easy to build a tsql OpenRowSet query which is a kind of SQL Server Ad Hoc Queries.
First check if on the current MS SQL Server instance Ad Hoc Distributed Queries are enabled or disabled.
In order to see if Ad Hoc Distributed Queries configuration option is set to 1 as run_value run the sp_configure system stored procedure.
EXEC sp_configure ;
If show advanced options SQL Server system configuration property for the sp_configure is set to 1, then you can see the "Ad Hoc Distributed Queries" entry in the result set.
Otherwise, you should enable "show advanced options" first, then by executing sp_configure sql stored procedure you can see the Ad Hoc Queries status on the Microsoft SQL Server 2005 or MS SQL Server 2008 instance.
In order to get help on how to enable Ad Hoc Distributed Queries please refer to sql tutorials How to enable Ad Hoc Distributed Queries on SQL Server by using sp_configure stored procedure.
SQL OpenRowSet Query
Lets first build our sql openrowset query.
SELECT RS.*
FROM OPENROWSET(
'SQLNCLI',
'SERVER=sqlservername;UID=sqluser;PWD=secretpassword;DATABASE=dbname;',
'SELECT Code, Name FROM dbname.dbo.States'
) AS RS;
The above query connects to the server name sqlservername with authentication information also given in the OpenRowSet query parameters.
The output of this example sql openrowset query is as follows :
Create SQL Encrypted Stored Procedure using With Encryption Option
After you showed that your sql openrowset ad hoc distributed query runs successfully, we can now create encrypted stored procedure.
In order to encrypt sql stored procedure, sql developers can use CREATE PROCEDURE command using With Encryption option.
Here is an example t-sql encrypted stored procedure created by With Encryption command.
CREATE PROCEDURE EncryptedStoredProcedure
WITH ENCRYPTION
AS
SELECT RS.*
FROM OPENROWSET(
'SQLNCLI',
'SERVER=sqlservername;UID=sqluser;PWD=secretpassword;DATABASE=dbname;',
'SELECT Code, Name FROM dbname.dbo.States'
) AS RS;
Now after the t-sql stored procedure is encrypted with encryption, even the creator user of this encrypted sql stored procedure can not display the contents of the procedure.
So an advice from me to sql developers and SQL Server database administrators who are willing to use sql stored procedures with encryption, please take a copy of your t-sql procedures source codes on an other safe database.
Because you can not display the content of the encrypted procedures using sp_helptext.
If you try to show source code of an t-sql encrypted procedure using sp_helptext as follows;
sp_helptext EncryptedStoredProcedure
The output from the sql engine will be as shown below :
The text for object 'EncryptedStoredProcedure' is encrypted.
So it is a good decision and a life-saving step to take backups of your sql encrypted with encryption stored procedures on an other database.
But this ability of keeping the source code of sql encrypted stored procedures enables sql developers and database admins to create sql ad hoc distributed queries like the sql openrowset query given as an example in this sql tutorial.
The sql login information, sql login name or sql login id and the sql user password are safe when the stored procedure is encrypted using "With Encryption" option during sp creation.