How to use establish a secure connection in code behind application page SharePoint 2010

Question

I need to solve the double hop issue for SharePoint 2010 application page. The ASP (C# code behind) page must connect securely to SQL server instance on another host. The secure store service appears to me to only work for a webpart. This can not be accomplished for the application page. Is there a secure alternative?

Current connection string is similar to:

private const string CONNECT_STR = @"Data Source=10.X.X.X,1433 ;Initial Catalog=TestCatalogName ; Integrated Security=false;User ID=testID;Password=********";
SqlConnection con = new SqlConnection(CONNECT_STR)

This seems like a common problem in SharePoint so I hope there is a corresponding common solution.

Answer 1

Only A webpart can use the secure store service in SharePoint 2010. The solution in place uses a SharePoint application page. I am connecting SQL Server and a viable alternative is to use a certificate for the encrypted connection.

See Enabling Encryption section in Using Encryption Without Validation in the client as in my case and concerned only with encryption over the connection. Both the client and server hosts are trusted.

See Selectively using secure connection to SQL Server. The table provides a good explanation of how to achieve the result of a my client using encrypted connection without impacting the SQL server and other connections to it.

The updated connection string is:

private const string CONNECT_STR = @"Data Source=10.X.X.X,1433 ;Initial Catalog=CM_ReleaseManagement ; Integrated Security=false; TrustServerCertificate=true; Encrypt=true; User ID=testID;Password=********";

See "Encrypting Connections to SQL Server" (insufficient reputation to post link) for instructions on configuring SQL Server per Microsoft when you need to encrypt all communications and higher level of security is required. No PII/PHI data involved.