none
Encrypt/deencrypt connection at runtime RRS feed

  • Question

  • I have the following problem:
    I use application role to secure the access to the DB from my application (c#).
    I want to secure the app role password sent from client to server when initialize connection encrypting this (SSL), thus obtaining a secure context.
    But the following operations on the db with this connection I don'e need/want to be secured, because of performance/overhead considerations.
    Can this be achieved ?
    i.e. disable encryption at runtime on an initial encrypted connection (SQLConnection).

    Thanks!
    Friday, July 11, 2008 8:07 AM

Answers

  • This is what MSDN says at http://msdn.microsoft.com/en-us/library/ms188908.aspx:

     

    To protect the application role password when it is transmitted across a network, you should always use an encrypted connection when enabling an application role..

    The Microsoft ODBC encrypt option is not supported by SqlClient.

     

    Since Encrypt is a part of the connection string you can't switch it off after the channel is created.

     

    Other options would be to use OdbcConnection instead of SqlConnection class to handle your database connectivity. ODBC driver support encryption:

     

    Code Snippet

    OdbcConnection c = new OdbcConnection("Driver={SQL Server};Server=servername;User ID=user;Password=password;");

    c.Open();

    OdbcCommand cmd = new OdbcCommand();

    cmd.Connection = c;

    cmd.CommandType = System.Data.CommandType.Text;

    cmd.CommandText = "sp_setapprole 'testrole', @password = {encrypt N'supersecretpassword'}, @encrypt=odbc";

    cmd.ExecuteNonQuery();

    c.Close();

     

     

    This is what you you get the wire:

     

    sp_setapprole 'testrole', @password =  0x92A5F2A5A2A5F3A582A592A5F3A593A582A5F3A5E2A5A2A5B3A592A592A5D2A553A582A5E3A5 , @encrypt=odbc
    Friday, July 11, 2008 5:03 PM