Storing External SQL connection string

[Former Member]

Hey All,

We are developing a custom solution that will require a connection to an SQL database outside of the B1 system database. Do any developers have previous experience with storing this information in a UDT, registry etc..,,? The information stored will be used to make a .NET SQl connection. Of course it would be encrypted where ever it is stored but I was hoping someone could share there experience with deploying this in a live application.

The the past we have used config files for storing any kind of configurable information that can be changed at runtime but that doesn't really work for the connection string as it would have to be changed as part of the install on the server. (It would be different for every customer install)

I am particularly interested in hearing from anyone who has deployed their solution to multiple clients and environments as this will be installed in a lot of locations.

Thanks,

Curtis

[Former Member · Accepted solution]

Anyone else have some experience with this?

[rasmuswulff_jensen · Accepted solution]

Hi Curtis

We have mostly encrypted our data using xml-files, but there should be no problem encrypting it to a UDT (as said above.. if all user should use the same encrypted data it should be in an UDT.. else a registry or XML-file)

You can find all kind of encryption samples but here are the one I currently use.

public abstract class Encryption
    {
        /// <summary>
        /// Encrypt a byte array into a byte array using a key and an IV 
        /// </summary>
        public static byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV)
        {
            MemoryStream ms = new MemoryStream();
            Rijndael alg = Rijndael.Create();
            alg.Key = Key;
            alg.IV = IV;
            CryptoStream cs = new CryptoStream(ms, alg.CreateEncryptor(), CryptoStreamMode.Write);
            cs.Write(clearData, 0, clearData.Length);
            cs.Close();
            byte[] encryptedData = ms.ToArray();
            return encryptedData;
        }

        /// <summary>
        /// Encrypt a string into a string using a password
        /// </summary>
        public static string Encrypt(string clearText, string Password)
        {
            byte[] clearBytes = System.Text.Encoding.Unicode.GetBytes(clearText);
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
                new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            byte[] encryptedData = Encrypt(clearBytes, pdb.GetBytes(32), pdb.GetBytes(16));
            return Convert.ToBase64String(encryptedData);
        }

        /// <summary>
        /// Encrypt bytes into bytes using a password 
        /// </summary>
        public static byte[] Encrypt(byte[] clearData, string Password)
        {
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,

                new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            return Encrypt(clearData, pdb.GetBytes(32), pdb.GetBytes(16));
        }

        /// <summary>
        /// Encrypt a file into another file using a password 
        /// </summary>
        public static void Encrypt(string fileIn, string fileOut, string Password)
        {
            FileStream fsIn = new FileStream(fileIn, FileMode.Open, FileAccess.Read);
            FileStream fsOut = new FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write);
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
                new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            Rijndael alg = Rijndael.Create();
            alg.Key = pdb.GetBytes(32);
            alg.IV = pdb.GetBytes(16);
            CryptoStream cs = new CryptoStream(fsOut, alg.CreateEncryptor(), CryptoStreamMode.Write);
            int bufferLen = 4096;
            byte[] buffer = new byte[bufferLen];
            int bytesRead;
            do
            {
                bytesRead = fsIn.Read(buffer, 0, bufferLen);
                cs.Write(buffer, 0, bytesRead);
            } while (bytesRead != 0);
            cs.Close(); // this will also close the unrelying fsOut stream 
            fsIn.Close();
        }

        /// <summary>
        /// Decrypt a byte array into a byte array using a key and an IV
        /// </summary>
        public static byte[] Decrypt(byte[] cipherData, byte[] Key, byte[] IV)
        {
            MemoryStream ms = new MemoryStream();
            Rijndael alg = Rijndael.Create();
            alg.Key = Key;
            alg.IV = IV;
            CryptoStream cs = new CryptoStream(ms, alg.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(cipherData, 0, cipherData.Length);
            cs.Close();
            byte[] decryptedData = ms.ToArray();
            return decryptedData;

        }

        /// <summary>
        /// Decrypt a string into a string using a password 
        /// </summary>
        public static string Decrypt(string cipherText, string Password)
        {
            byte[] cipherBytes = Convert.FromBase64String(cipherText);
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
                new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            byte[] decryptedData = Decrypt(cipherBytes, pdb.GetBytes(32), pdb.GetBytes(16));
            return System.Text.Encoding.Unicode.GetString(decryptedData);
        }

        /// <summary>
        /// Decrypt bytes into bytes using a password
        /// </summary>
        public static byte[] Decrypt(byte[] cipherData, string Password)
        {
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
                new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            return Decrypt(cipherData, pdb.GetBytes(32), pdb.GetBytes(16));
        }

        /// <summary>
        /// Decrypt a file into another file using a password
        /// </summary>
        public static void Decrypt(string fileIn, string fileOut, string Password)
        {
            FileStream fsIn = new FileStream(fileIn, FileMode.Open, FileAccess.Read);
            FileStream fsOut = new FileStream(fileOut, FileMode.OpenOrCreate, FileAccess.Write);
            PasswordDeriveBytes pdb = new PasswordDeriveBytes(Password,
                new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            Rijndael alg = Rijndael.Create();
            alg.Key = pdb.GetBytes(32);
            alg.IV = pdb.GetBytes(16);
            CryptoStream cs = new CryptoStream(fsOut, alg.CreateDecryptor(), CryptoStreamMode.Write);
            int bufferLen = 4096;
            byte[] buffer = new byte[bufferLen];
            int bytesRead;
            do
            {
                bytesRead = fsIn.Read(buffer, 0, bufferLen);
                cs.Write(buffer, 0, bytesRead);
            } while (bytesRead != 0);
            cs.Close(); // this will also close the unrelying fsOut stream 
            fsIn.Close();

        }
    }

[Former Member]

Hi Curtis,

I have created UDT and when my add-on starts, I try to connect to SQL Server using that connection string. If it fails, I will open .NET form that prompts for SQL SERVER IP, DB Name, DB User Id & DB Password. If connection fails with provided information, show the error message. If SQL Server gets connected continue with Add-On execution.

So only when Add-on is starting it prompts for SQL Server Details since connection string is empty. Otherwise if there is any change in SQL Server Details I prompts.

Since these details stored in UDT of each company I had more flexibility to configure SQL Server Details for each company.

Since I stored this information in centralized DB it is accessible to all clients.

Storing in registry lead to storing for SQL Connection details in each client machine.

This is applicable only if we are using different SQL Server Instance. If we are accessing details from same database, we can access using <SERVER NAME>.dbo.<TABLE NAME> format.

HTH

B Ravi Shankar

[Former Member]

Thanks B Ravi,

That sounds like a good solution. Do you encrypt or protect the information in this UDT so that it cannot be viewed or edited manuallY? Would you have some code you would be willing to share on the forum to help with this.