Decision Support

Enable Encryption on your Connection to EDW data

Encryption is the scrambling of data so only the intended users can read and understand the encrypted information. Encrypting the SQL connection to data on the Enterprise Data Warehouse is very important. Without encryption the data sent between the server and the client is readable by anyone that can intercept your network packet.  If you access confidential data, it is considered a basic security practice to encrypt the communications between you and the server.

How to Enable Encryption:

For SQL Server 2005:

Automatically Encrypted Connection
Data housed on the following servers is encrypted when sent between the server and client.  Users do not need to take any further action.

[SQL 2005]

  • EDWSQLC1DB1
  • EDWSQLC1DB2\SQL02
  • EDWETL1
  • EDWTEST1\INTEG
  • EDWTEST1\OPER
  • EDWTEST1\PRES
  • EDWTEST1\STAGE

[SQL 2008]  

  • OTTAWA\BIdev    

Unencrypted Connection
EDW data housed on the UCSSQLC1DB1\ODS, UCSSQLC1DB2\ONLINE servers, which covers the DAC-secured versions of the UWSDBDataStore and ODS databases, does not have encryption automatically applied. Why not set encryption, you may ask?  This server lives outside the EDW realm and other activities/process affect the ability to set that setting. As such, users must apply encryption.

Enabling Encryption For SSMS (SQL Server Mgmt Studio) Connections

The first two screens show how to find the encrypt connection setting when you simply open a new connection in SSMS.  The third screen shows where it is if you use “registered servers” in SSMS. 

connect to server

 

connect to server

 

 Edit Server Reg Properties

Enabling Encryption for ODBC (Microsoft Access) Connections

This screen shows the box to check when setting up one’s ODBC connection.  You can find complete instructions on creating an ODBC connection here.

SQL Server DSN Configuration

For BIDS:

From within BIDS, double-click on a Shared Data Source object.

The outcome is the addition of ;Encrypt=True at the end of the connection string.  You could also hand-type that without having to click through the screens.