Create Encrypted Views using Openrowset to Connect Remote Databases with T-SQL Sample Code
It is a security requirement especially on production SQL Server databases, to connect to a remote server and run a select query over a sql database view but to hide or encrypt the database connection string or the database user and password.
In my case, I had to serve data to another application which is running as a third party QPR application in my company.
The main requirement can be summarized as keeping the connection properties safe and secure.
Since both applications, the data source and the target application are on remote database servers, and there is not a linked server relation between the two servers I had to use the OPENROWSET t-sql command in the select query for data source.
After I had connected to the source sql database by using OPENROWSET, I had encrypted the view using WITH ENCRYPTION hint extensions to the CREATE VIEW command.
You know creating a view with WITH ENCRYPTION enables sql programmers to hide the meta text or the inner select query of the sql view.
In sql development, sp_helptext t-sql command displays the sql context of a view, stored procedure or a user defined function.
But if a sql developer creates the sql database view by using With Encryption clause, sp_helptext will not be able to help unauthenticated users to display the context or the select query in the related view.
Let's assume the target MS SQL Server instance is installed on server "server" and the instance name is "instance".
Let the database user name be dbuser and the password to password.
And assume that we want to connect to the database named db.
Here below is the sql databases OpenRowset syntax :
SELECT FS.*
FROM OPENROWSET(
'SQLNCLI',
'Server=server\instance;Uid=dbuser;Pwd=password;Database=db',
'SELECT * FROM dbo.MySampleSQLTable'
) AS FS;
You can use the above OPENROWSET syntax to connect to an other Microsoft SQL Server database using the SQLNCLI database provider.
You should remove the "[" and "]" in the name of the SQL Server database instance if you are using them.
I mean, if you are using the SQL database server name just as you use do in linked servers, the server engine will throw an error.
So for a sample, use "server_name\instance_name" not "[server_name\instance_name]"
For encrypting a sql view, sql programmers can use the below sample t-sql codes :
CREATE VIEW Encrypted_SQL_View_name With ENCRYPTION
Here is the below sql code to create the encrypted sql view.
CREATE VIEW Encrypted_SQL_View_name With ENCRYPTION
AS
SELECT FS.*
FROM OPENROWSET(
'SQLNCLI',
'Server=server\instance;Uid=dbuser;Pwd=password;Database=db;',
'SELECT * FROM dbo.MySampleSQLTable'
) AS FS;
GO
To summarize, for sql developers, encrypting a sql database view and using a OPENROWSET in the covered sql statement, programmers can server sql data in secure to third party applications.