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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems