none
how to encrypt a connection string

    Question

  • I am using VB.NET 2015, windows forms application, datasets with some entity framework and SQL 2014 server

    How do I encrypt the connection string (primarily the password) used for connecting to SQL Server? I created the datasets in the designer, and the connection string is saved in the application settings tab. 

    Thank you. -ME

    Monday, May 1, 2017 3:54 PM

All replies

  • Use integrated security. 

    It makes no sense to encrypt a connectionstring when it is Server Security. The Server expects an unencrypted string. 


    Success
    Cor

    Monday, May 1, 2017 4:14 PM
  • I am using VB.NET 2015, windows forms application, datasets with some entity framework and SQL 2014 server

    How do I encrypt the connection string (primarily the password) used for connecting to SQL Server? I created the datasets in the designer, and the connection string is saved in the application settings tab. 

    Thank you. -ME

    Hi

    Here is some code, taken directly from the MS documentation that will Encrypt/DeCrypt to/from a user setting (here called 'ConString')

    There is a Class which does the Encryption/Decryption and some basic Form1 code to call the Class code. It does seem a bit overkill for dealing with one string,but there you are.

    CLASS CODE

    ' from https://msdn.microsoft.com/en-us/library/ms172831.aspx
    
    Option Strict On
    Option Explicit On
    Imports System.Security.Cryptography
    Public Class Simple3Des
        Private TripleDes As New TripleDESCryptoServiceProvider
        Sub New(ByVal key As String)
            ' Initialize the crypto provider.
            TripleDes.Key = TruncateHash(key, TripleDes.KeySize \ 8)
            TripleDes.IV = TruncateHash("", TripleDes.BlockSize \ 8)
        End Sub
        Public Function EncryptData(
                ByVal plaintext As String) As String
            ' Convert the plaintext string to a byte array.
            Dim plaintextBytes() As Byte = Text.Encoding.Unicode.GetBytes(plaintext)
            ' Create the stream.
            Dim ms As New IO.MemoryStream
            ' Create the encoder to write to the stream.
            Dim encStream As New CryptoStream(ms, TripleDes.CreateEncryptor(), CryptoStreamMode.Write)
            ' Use the crypto stream to write the byte array to the stream.
            encStream.Write(plaintextBytes, 0, plaintextBytes.Length)
            encStream.FlushFinalBlock()
            ' Convert the encrypted stream to a printable string.
            Return Convert.ToBase64String(ms.ToArray)
        End Function
        Public Function DecryptData(ByVal encryptedtext As String) As String
            ' Convert the encrypted text string to a byte array.
            Dim encryptedBytes() As Byte = Convert.FromBase64String(encryptedtext)
            ' Create the stream.
            Dim ms As New IO.MemoryStream
            ' Create the decoder to write to the stream.
            Dim decStream As New CryptoStream(ms, TripleDes.CreateDecryptor(), CryptoStreamMode.Write)
            ' Use the crypto stream to write the byte array to the stream.
            decStream.Write(encryptedBytes, 0, encryptedBytes.Length)
            decStream.FlushFinalBlock()
            ' Convert the plaintext stream to a string.
            Return Text.Encoding.Unicode.GetString(ms.ToArray)
        End Function
        Private Function TruncateHash(ByVal key As String, ByVal length As Integer) As Byte()
            Dim sha1 As New SHA1CryptoServiceProvider
            ' Hash the key.
            Dim keyBytes() As Byte =
                Text.Encoding.Unicode.GetBytes(key)
            Dim hash() As Byte = sha1.ComputeHash(keyBytes)
            ' Truncate or pad the hash.
            ReDim Preserve hash(length - 1)
            Return hash
        End Function
    End Class

    FORM1 CODE (needs TextBox1, TextBox2, Button1 and Button2)

    TextBox1 for first use setting new con string

    TextBox2 for password

    Button1 to Encypt TextBox1 text to My.Settings.ConString

    Button2 to Decrypt fromMy.Settings.ConString using password in TextBox2.

    ' Form1 with TextBox1,TextBox2, Button1 and Button2
    Option Strict On
    Option Explicit On
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ' encrypt
            Dim wrapper As New Simple3Des(TextBox2.Text)
            Dim cipherText As String = wrapper.EncryptData(TextBox1.Text)
            MessageBox.Show("The cipher text is: '" & cipherText & "'")
            My.Settings.ConString = cipherText
        End Sub
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim wrapper As New Simple3Des(TextBox2.Text)
            ' DecryptData throws if the wrong password is used.
            Try
                Dim plainText As String = wrapper.DecryptData(My.Settings.ConString)
                MessageBox.Show("The plain text is: '" & plainText & "'")
            Catch ex As Security.Cryptography.CryptographicException
                MessageBox.Show("The data could not be decrypted with the password.")
            End Try
        End Sub
    End Class



    Regards Les, Livingston, Scotland


    • Proposed as answer by Cor LigthertMVP Monday, May 1, 2017 6:47 PM
    • Edited by leshay Friday, May 5, 2017 11:32 PM
    Monday, May 1, 2017 4:51 PM
  • With Entity Framework code first you can decrypt in the model's constructor but not with model first (using a edmx). So if you encrypt with code first you are fine, with model first you can't as the constructor can not be overloaded or overridden. The connection for EF will not show up under project properties/settings either.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, May 1, 2017 6:04 PM
    Moderator
  • @Les and in fact @Karen,

    I've seen those samples of course as well in past. In the sample is nothing about how to use it. Only how to encrypt it. Like the OP wrote can it be useful in SQL Server Security if passwords are used. However, even then I don't know any situation where the password goes over the line and if it is (connecting direct to a server on Internet), then there is not any way to use it. 

    Maybe does one of you have a usefulness reason why to use it?

    I know it is often told on Internet to do it, but never why and how  (complete, because the cryptography namespace has hundred other ways).


    Success
    Cor

    Monday, May 1, 2017 6:27 PM
  • @Les and in fact @Karen,

    I've seen those samples of course as well in past. In the sample is nothing about how to use it. Only how to encrypt it. Like the OP wrote can it be useful in SQL Server Security if passwords are used. However, even then I don't know any situation where the password goes over the line and if it is (connecting direct to a server on Internet), then there is not any way to use it. 

    Maybe does one of you have a usefulness reason why to use it?

    I know it is often told on Internet to do it, but never why and how  (complete, because the cryptography namespace has hundred other ways).


    Success
    Cor

    Hi Cor

    I only supplied the example as an illustration with no regard for the use of it. From my understanding, the OP wanted to store an encrypted password in a user setting - that is all I tried to show.


    Regards Les, Livingston, Scotland

    Monday, May 1, 2017 6:34 PM

  • I only supplied the example as an illustration with no regard for the use of it. From my understanding, the OP wanted to store an encrypted password in a user setting - that is all I tried to show.


    Regards Les, Livingston, Scotland

    Yes maybe, because if a disk is detached from a computer you can see the folder and then find that password if you attach it to another computer. 

    Further no comment. 

    :-)


    Success
    Cor


    Monday, May 1, 2017 6:46 PM
  • I am using VB.NET 2015, windows forms application, datasets with some entity framework and SQL 2014 server

    How do I encrypt the connection string (primarily the password) used for connecting to SQL Server? I created the datasets in the designer, and the connection string is saved in the application settings tab. 

    Thank you. -ME

    Why wouldn't you just require the user to enter the password ?
    Monday, May 1, 2017 7:02 PM
  • Cor,

    Literally I indicated you can't use encryption with Entity Framework with a connection string in the config file as the base constructor for the DbContext can't be altered.

    Now if we were to consider an alternate method that could go in many directions dependent on the environment be it a private computer or running on a dedicated server which I did not even attempt to get into.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, May 1, 2017 7:10 PM
    Moderator
  • Karen,

    Never mind, but the OP was also writing he used the dataset designer. Because some replies here, I avoid telling what I think of that. 

    :-)

    (Or there should something I totally miss)


    Success
    Cor

    Monday, May 1, 2017 7:17 PM
  • Thank you. Can this be done at the Application scope level? My dataset connection strings are automatically created and saved with a (Connection string) Application settings.
    Tuesday, May 2, 2017 3:37 PM
  • Thank you. Can this be done at the Application scope level? My dataset connection strings are automatically created and saved with a (Connection string) Application settings.

    Can you elaborate a little bit what means here "Application scope level"

    It exist in Web development in Session and Application scope, but inside desktop development with VB it is at least for me something totally new. 


    Success
    Cor

    Tuesday, May 2, 2017 3:47 PM
  • Thank you Karen. I am primarily using datasets throughout the application. I am working to learn Entity Framework, but I do not yet have enough of a grasp to start converting the datasets to EF. With that said, I was asked to change the data connection authentication from Windows Auth, to single account SQL Server Auth for the client application. I was also asked to encrypt the saved password in the connection string. I am referring to the Application scope level, connection string type in the application settings tab (which I understand is saved in the app config file). I have been researching this for a while now and I feel I might be missing something, because this doesn't seem to be something people do? I've found a lot of discussions for encrypting app config files for web applications (ASP.NET), but nothing specifically for connection strings in Windows Forms applications. Is the app config file actually installed on the user's system when the application is released/deployed? When I publish the application, I do see a WinFormsApplication.exe.config.deploy file in the Application Files folder, but how is this used when the user installs the application? Any additional guidance would be greatly appreciated. -ME
    Tuesday, May 2, 2017 4:03 PM
  • Tuesday, May 2, 2017 4:11 PM
  • Thanks,

    I see now what you means, but I assume that here counts as well what Karen told about the Entity Framework. 

    If you have decrypted it, it will automatically be stored back in the setting decrypted. While a connectionstring can only be used from there otherwise it is private and not reachable. 

    But maybe somebody else knows a solution. I'm curious too.

    Sorry I cannot help you.


    Success
    Cor

    Tuesday, May 2, 2017 4:44 PM
  • I am referring to the Application scope level, connection string type in the application settings tab (which I understand is saved in the app config file). I have been researching this for a while now and I feel I might be missing something, because this doesn't seem to be something people do? I've found a lot of discussions for encrypting app config files for web applications (ASP.NET)

    Your required procedure is not clear.

    Do you mean that you want to encrypt the password before transmitting it to the server as part of the connection string?  If this works for your connection, then it is the encrypted (ie, hashed) password that is stored locally, and there is no need to encrypt/decrypt anything.

    If you mean that you want to transmit the password (or the whole connection string) but you want to store it locally encrypted so it is not discoverable, then it is the value you store into the application setting that needs to be encrypted - you would not encrypt the application settings file.  Or, as per the discussion here:
      https://msdn.microsoft.com/en-us/library/ms171887.aspx
    you would build the connection string from a combination of the saved connection string and information entered by the user.

    Wednesday, May 3, 2017 12:46 AM
  • @Acamar,

    That article is from around 2002. Beside the part of integrated security it can only be used if you don't use generic created datasets and adapters. 

    Something many MVP's have stumbled around in past, but there was never done anything about it. 

    (Or you should be know a way how to add yourself a connectionstring to a generic created DataAdapter (and everything derived from it) in code. 


    Success
    Cor

    Wednesday, May 3, 2017 7:57 AM
  • That article is from around 2002. Beside the part of integrated security it can only be used if you don't use generic created datasets and adapters.

    Yes.  But that seems to be what OP wants to do: "I am referring to the Application scope level, connection string type in the application settings tab (which I understand is saved in the app config file)."  The comments above make it clear that there are better ways to do it, but OP is just following instructions.

    Wednesday, May 3, 2017 8:02 AM
  • One option is to use the built in DataProtectionConfigurationProvider to encrypt your app.config setting:

    Securing Connection Strings 


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 3, 2017 12:49 PM