none
Protect[hide] MSSQL Stored password RRS feed

  • Question

  • I wrote a program in vb.net that connects to a database.  The info to connect to a database Server=[DataBaseIP];uid=[UserName];pwd=[Password];Database=[DatbaseName];Trusted_Connection=no  can be clearly seen when I drop my program into notepad.    Is there any way to protect this information, so others can not see it?

    THanks

    Monday, November 11, 2019 4:28 PM

Answers

  • Hi

    One way of the many possibilities. Use the My.Settings at store all the values, but, encrypt the values and decrypt for use. There has to be a 'key' for use in the encryption, and depending on the level of security you want, that could be left plain text (but could be an ordinary phrase that wouldn't look like a 'key'. If you need a higher level of security then other contributors  may be better placed to help.

    Here is some code. The first code block is a Class that has the Encrypt and Decrypt code, and the following code shows how to use it. Of course, you are only using a handful of values, but the same principles apply. This is a stand alone example and needs the controls on a Form1 as set out below.

    ' 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 with ListBox1 (Start list)
    ' and ListBox2 (Encrypted/Decrypted)
    ' Button1 (Encrypt) and 
    ' Button2 (Decrypt)
    Option Strict On
    Option Explicit On
    Public Class Form1
    	'-----------------------------
    
    	Dim key As String = "EncypherKey"
    	Dim wrapper As New Simple3Des(key)
      '-----------------------------
    
      Dim PlainText As New List(Of String)
      Dim Encoded As New List(Of String)
      Dim Decoded As New List(Of String)
      Dim lengths As New List(Of Integer)
      Dim r As New Random
      Dim alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
      Dim Numeric As String = "1234567890"
      Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' some random test data
        Dim all As String = alpha & Numeric & alpha.ToLower
        Dim len As Integer = 11
    		For i As Integer = 0 To 10
    			Dim start As Integer = r.Next(0, all.Length - len + 1)
    			Dim entry As String = all.Substring(start, len)
    			PlainText.Add(entry)
    			ListBox1.Items.Add(entry)
    		Next
    	End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' encode and measure 11111 strings
        For Each s As String In PlainText
          Dim en As String = wrapper.EncryptData(s)
          lengths.Add(en.Length)
          Encoded.Add(en)
        Next
        ListBox2.DataSource = Encoded
      End Sub
      Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' decode all
        For Each s As String In Encoded
          Decoded.Add(wrapper.DecryptData(s))
        Next
        ListBox2.DataSource = Decoded
      End Sub
    End Class



    Regards Les, Livingston, Scotland


    Monday, November 11, 2019 4:51 PM

All replies

  • Hi

    One way of the many possibilities. Use the My.Settings at store all the values, but, encrypt the values and decrypt for use. There has to be a 'key' for use in the encryption, and depending on the level of security you want, that could be left plain text (but could be an ordinary phrase that wouldn't look like a 'key'. If you need a higher level of security then other contributors  may be better placed to help.

    Here is some code. The first code block is a Class that has the Encrypt and Decrypt code, and the following code shows how to use it. Of course, you are only using a handful of values, but the same principles apply. This is a stand alone example and needs the controls on a Form1 as set out below.

    ' 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 with ListBox1 (Start list)
    ' and ListBox2 (Encrypted/Decrypted)
    ' Button1 (Encrypt) and 
    ' Button2 (Decrypt)
    Option Strict On
    Option Explicit On
    Public Class Form1
    	'-----------------------------
    
    	Dim key As String = "EncypherKey"
    	Dim wrapper As New Simple3Des(key)
      '-----------------------------
    
      Dim PlainText As New List(Of String)
      Dim Encoded As New List(Of String)
      Dim Decoded As New List(Of String)
      Dim lengths As New List(Of Integer)
      Dim r As New Random
      Dim alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
      Dim Numeric As String = "1234567890"
      Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' some random test data
        Dim all As String = alpha & Numeric & alpha.ToLower
        Dim len As Integer = 11
    		For i As Integer = 0 To 10
    			Dim start As Integer = r.Next(0, all.Length - len + 1)
    			Dim entry As String = all.Substring(start, len)
    			PlainText.Add(entry)
    			ListBox1.Items.Add(entry)
    		Next
    	End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' encode and measure 11111 strings
        For Each s As String In PlainText
          Dim en As String = wrapper.EncryptData(s)
          lengths.Add(en.Length)
          Encoded.Add(en)
        Next
        ListBox2.DataSource = Encoded
      End Sub
      Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' decode all
        For Each s As String In Encoded
          Decoded.Add(wrapper.DecryptData(s))
        Next
        ListBox2.DataSource = Decoded
      End Sub
    End Class



    Regards Les, Livingston, Scotland


    Monday, November 11, 2019 4:51 PM
  • A modern path is to use Credential property of the Connection object.

    The SqlCredential class.

    For a quick look at base implementation see my code sample on GitHub. user name and password are not passed in the connection string so there is no access to either user name or password, only between a login form and the code which uses the user name and password in the class to login where in my code is encrypted.

    In the code sample I include a login window that can be used in any project.

    See also, TechNet Wiki article SQL-Server database login for Windows Forms.

    In closing don't simply try and copy-n-paste my code into a project without understanding the code.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, November 11, 2019 5:53 PM
    Moderator
  • Hi,

    You could use the ProtectSection method to encrypt  and configure a Connection String in the App.Config.

    Please refer to the link:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e17b1fe3-829e-49b8-9c11-3687f8753a93/how-to-encrypting-and-configuring-a-connection-string-in-the-appconfig-by-vbnet?forum=vbgeneral

    Also, the following link provides several other ways to protect the connection string, which will certainly be useful:

    https://www.codeproject.com/Questions/1195642/Encrypt-database-string-VB-NET

    Last, if your program is connected to the server, you can call the server-side API on the client -> connect to the database on the server side -> return data to the client.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 12, 2019 6:40 AM
    Moderator
  • Here is an example of protecting a config file as per Julie's post.

    https://social.technet.microsoft.com/wiki/contents/articles/52301.vb-net-secure-connection-string-for-windows-forms.aspx

    Potential issues

    While performing a data operations a runtime exception is thrown without proper assertion in the form of a try/catch/finally statement the configuration file will be left exposed. The worst case is power is lost and the application crashes with an exposed configuration file. No matter, it's better to have encryption than not to have encryption. This can be circumvented by ensuring the file is encrypted (except for power lose) by checking if the config file is encrypted at program end under My Application events as the code in the article has a test to see if the connection string is encrypted.



    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, November 12, 2019 11:32 AM
    Moderator
  • Thanks everyone for all the info,  I think I am now onto the correct path with this.

    Thanks everyone for the input

    Wednesday, November 13, 2019 12:52 AM
  • Hi,

    Thank you for your advice, Karen.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 13, 2019 7:05 AM
    Moderator