We help IT Professionals succeed at work.

Some Encryption/Decryption Questions

I am using Microsoft Pro Sync Franework to synchronize a SQL Server CE 3.5 database with SQL Server 2008 R2 Express. I have a few question:

I know how to encrypt the connection string in app.config. How do I decrypt it on the server side so it is not transmitted in plain text?

The database is relatively small. How would I programmatically encrypt the entire db on both computers and then maintain synchronization? It would not work well if the encryption depended on the user/machine combination. I would need to use a machine independent method.
Comment
Watch Question

Commented:
You should force SSL connections on the SQL server and use that instead.
Commented:
Here is how to configure the SQL server

Author

Commented:
Thanks for your quick post.
Unfortunately I have a SSL certificate from a trusted CA that won't work because the SQL Server Express server is behind a router using port forwarding. Hence, I can't use aliasing. I'll look into a self-signed certificate and force the trust.

Author

Commented:
The link you provided did help me. I was able to find the thumbprint (although Firefox calls it a fingerprint) and put that in the registry. The service has started up again. It may take a bit of testing to get definitive results that the SSL is working properly, but it should work when the connection string is changed from http:// to https://.

Author

Commented:
I appreicate your help.

Commented:
Glad to have helped you get it working.

Author

Commented:
Here is what I had to do for the connection string:
        Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder
        With builder
            .DataSource = Trim(txtServer.Text)
            .UserID = Trim(txtLoginName.Text)
            .Password = Trim(txtPassword.Text)
            .IntegratedSecurity = False
            .PersistSecurityInfo = True
            .InitialCatalog = "HouseholdInventory"
            .Encrypt = True
            .TrustServerCertificate = True
        End With

Open in new window