Best practice for store db string connection

[hannibal smith]

I need to distribuite a .NET 6 Winform application, that work with a db.
Since no one should know db password, which is your best practice to store the string connection and credentials ?

[vbdotnut]

Just bake it in to your source code. (not in app settings) it would take great effort to decompile your app to read this string. Unless youre sharing the sourcecode this is a perfectly safe way to do it.

As for best practice you will find that this is where MSSQL will shine. You can simply use integrated security and not deal with storing any authentication strings, but obviously thats not always an option, and its an expensive option.

I reckon that you could compile your app with the proper password, and after the build, change the pwd in the sourcecode and save it.

[kebo]

it would take great effort to decompile your app to read this string..

Out of the box, .Net code can be easily decompiled with a number of free .Net decompilers. You can have some protection using obfuscation, but even then, strings are not always protected.
kevin

Edit, I'm speaking from experience with .Net Framework apps. I'm not sure if the .Net6 platform de/compiles the same. I assumed they would.

[hannibal smith]

I made some test and you are right.

[Zvoni]

Credentials (User and Password) should never be stored in an app.
Just to be obnoxious, i install your app, and since it's my DB-Server i change the login-credentials on the server itself, and your App goes KABOOM.

There is no scenario with "no one should know the db-password".
if it's an initial password for first run, provide that password, but force the user (or whoever) to change it

[sapator]

You say db.
If it's a local DB then we got issues. If it just calls a remote db then using only read permission and a hashed password should be no problem and also specifying which sp's the user account can run.
On local DB I'm not sure. You certainty can have a hashed password but as Zvoni said you can change it an mess it up. Also if you know or guess the hash algorithm you can hash your own pass, update the db with that one and voila! You just cracked the password.
So a local program with a local db is not really safe. But why should you bother if the user is local?

[hannibal smith]

this db contains I'm using a local db to store binary patterns that my app will find into user's file. Since they are not public, I would share the whole db; I known that one can get this patterns looking to the results, but it take long time, accessing the db give all patterns in "no time".. So I think I'll take the advice to move it to remote.

[Zvoni]

"local DB needing a password" --> sounds like SQLite with encryption.

If you really have to store that Password in your App, i'd never use plain text, rather the Password encrypted (encrypted with some algorithm of your choice), with some "unreadable" (as in plain Text) Key

Something along the lines....

'Somewhere in your Code
Dim MyKey As String = "hdslsrur<fjivjyfnlwanvyor94p24u@2934("
Dim MyPW As String = "klhghwzufg/34739/&?!(/@rurn256"

and before you access your DB, you decrypt your Password

Even if it's still bad practice to store something like that in your executable, it's way better than plain text

[sapator]

That would be decrypted in a second by a first grader programmer, without obfuscation (that costs money).
In general stand alone applications with .NET don't really stand a chance. As we have seen almost all apps will go online to get a key so they can be enabled. So if you are going online anyhow, store something there.
Or at worse write a calling C++ dll and store they keys there. That is a more sweating for a hacker than a .net string.

[szlamany]

Found this post I made here on the forum on 12/20/2021...it's related to a web.config file. Doesn't the connection string you are talking about get stored in some application config file? Maybe they work the same with the info below?

https://www.vbforums.com/showthread.php?894597

Found this link - and it worked perfectly

https://techcommunity.microsoft.com/...on/ba-p/830094

Run Command Prompt as Administrator

Go to C:\Windows\Microsoft.NET\Framework\v4.0.30319

Perform the command below to encrypt the connection string in your web.config:

ASPNET_REGIIS -pef "connectionStrings" "D:\inetpub\wwwroot\applicationFolder"

Open web.config and check if the connection string is encrypted

Test the site

If you want to decrypt it back, run this command:

ASPNET_REGIIS -pdf "connectionStrings" "D:\inetpub\wwwroot\applicationFolder"

Open the web.config and check if the connection string is decrypted

[sapator]

If I'm not mistaken this is server side, or if you call your home PC a server.
So,yeah but it won't do much on stand alone apps.

[vbdotnut]

Kebo is correct in that it is doable to decompile the app and scour through bits of source code, i suppose I shall rephrase and say that it would take an extended effort, not necessarily a great effort.

I guess if the app is such that James Bond commandeers your computer to get your offshore bank accounts you can employee a more robust encryption strategy. In this way you could also allow your user to change and store the password. in the app settings.

Here's a set of functions that will do just that

Private key As String = "YourEncryptionKey123456789012345" ' 32-byte key for AES-256 (needs to be 32 char)
Private iv As String = "YourEncryption01" ' 16-byte IV for AES (needs to be 16 char)

    Public Function EncryptString(ByVal inputString As String) As String
        Using aesAlg As Security.Cryptography.Aes = Security.Cryptography.Aes.Create()

            Dim keyBytes As Byte() = Encoding.UTF8.GetBytes(key)
            Dim ivBytes As Byte() = Encoding.UTF8.GetBytes(iv)
            Dim keyBase64 As String = Convert.ToBase64String(keyBytes)
            Dim ivBase64 As String = Convert.ToBase64String(ivBytes)

            aesAlg.Key = Convert.FromBase64String(keyBase64)
            aesAlg.IV = Convert.FromBase64String(ivBase64)

            Dim encryptor As Security.Cryptography.ICryptoTransform = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV)

            Using msEncrypt As New IO.MemoryStream()
                Using csEncrypt As New Security.Cryptography.CryptoStream(msEncrypt, encryptor, Security.Cryptography.CryptoStreamMode.Write)
                    Using swEncrypt As New IO.StreamWriter(csEncrypt)
                        swEncrypt.Write(inputString)
                    End Using
                End Using

                Dim encryptedBytes As Byte() = msEncrypt.ToArray()
                Dim encryptedString As String = Convert.ToBase64String(encryptedBytes)
                Return encryptedString
            End Using
        End Using

    End Function

    Public Function DecryptString(ByVal encryptedString As String) As String
        Using aesAlg As Security.Cryptography.Aes = Security.Cryptography.Aes.Create()
            Dim keyBytes As Byte() = Encoding.UTF8.GetBytes(key)
            Dim ivBytes As Byte() = Encoding.UTF8.GetBytes(iv)

            Dim keyBase64 As String = Convert.ToBase64String(keyBytes)
            Dim ivBase64 As String = Convert.ToBase64String(ivBytes)

            aesAlg.Key = Convert.FromBase64String(keyBase64)
            aesAlg.IV = Convert.FromBase64String(ivBase64)

            Dim decryptor As Security.Cryptography.ICryptoTransform = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV)
            Dim encryptedBytes As Byte() = Convert.FromBase64String(encryptedString)

            Using msDecrypt As New IO.MemoryStream(encryptedBytes)
                Using csDecrypt As New Security.Cryptography.CryptoStream(msDecrypt, decryptor, Security.Cryptography.CryptoStreamMode.Read)
                    Using srDecrypt As New IO.StreamReader(csDecrypt)
                        Dim decryptedString As String = srDecrypt.ReadToEnd()
                        Return decryptedString
                    End Using
                End Using
            End Using
        End Using

    End Function

which you can use like so:

'Put the user's encrypted pwd in settings
My.Settings.Password = EncryptString(UserPwdTBox.Text)

'Get the user pwd from settings for connstring
Dim SQLHost As String = "Your SQL Server"
Dim SQLDB As String = "Your DB"
Dim Usr As String = "User Name"
Dim PWD As String = DecryptString(My.Settings.Password)
Dim SQLConnStr As String = $"Data Source={SQLHost};Initial Catalog={SQLDB};User ID={Usr};Password={PWD}"

now you can argue that you can also get the keys out through decompile as well and reverse engineer the encryption, so the idea is to put the keys someplace else such as environment variables, which we can go down that road if your situation really requires this level of protection

[sapator]

I'm wondering, why do we go to such trouble to write an encryption an we have the keys right in the open?
Without obfuscation we can get the keys by even possibly just opening the dll on a text editor.
I would say that either store them remotely or create a simple c++ dll and reference the keys from there. Better yet do something like 1=A 2=C 3=F and for a number 312 to words FAC so it will confuse the assembler even more.
But holding important information on a the user side and trying to "hide" they keys at .net level is not going to work if someone wants to get the keys.
Or I'm wrong and there is some secure what that we haven't talked yet....
OH, I have an idea about doing this in VB6

[vbdotnut]

I'm wondering, why do we go to such trouble to write an encryption an we have the keys right in the open?

I went through this trouble ages ago and for the simple reason i didnt want anyone to simply open the settings file and read password and it is the least of my concern that anyone anywhere would go through such trouble to decompile the app, and then what? Youd need to copy the encrypted pwd from settings, get the keys and maybe find an online decryption tool or make your own... It is safe to say that nobody is going to do this in a workplace, there would have to be something pretty substantial to be gained. As I mentioned there are ways keep the key separate to your .Net environment as well

[sapator]

Note, I'm not calling you off. I just say that .net locally is not safe and proposed the safest way I might have think of.
Heck I'll ever rep you but again it's completely different on what I propose.
Note you can inject a key in .net but of course I won't write how, granted it's a bit troublesome but it's not that difficult to recompile the project from the dll but I won't write that down also.

[vbdotnut]

Another option could be to ask the user for a x length char pin that is not stored that you could append to the key before passing it to the encryption functions.