SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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 * 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 :


Here is the below sql code to create the encrypted sql view.


  'SELECT * FROM dbo.MySampleSQLTable'
) AS FS;


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.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.