How to Encrypt SQLite

[Erdem]

 

Hi,

 I'm using "schemahandler.createdatabase" and "createDDLScript" for creating and updating a database.

 I would like to password protect the database and I know it's possible with the SQLite driver that is available for .net

Like so:

 

//if the database has already password
try{
            Conn = @"Data Source=database;Password=Mypass;";
            SQLiteConnection connection= new SQLiteConnection(Conn);
            connection.Open();
            //Some code
            connection.ChangePassword("Mypass");
            connection.Close();
    }
//if it is the first time sets the password in the database
catch
    {
            Conn = @"Data Source=database;";
            SQLiteConnection connection= new SQLiteConnection(Conn);
            connection.Open();
            //Some code
            connection.ChangePassword("Mypass");
            connection.Close();
            Conn = @"Data Source=database;Password=Mypass;";
    }

 ---

What I have tried to do is the following:

if db is not yet created => allow schemahandler to create it with regular connectionstring, then execute classic ado commands(as stated in above post) to add password to database and modify a static connectionstring (Conn) to a connectionstring with password.

if db already exists => execute schemaupdates and then update password and connectionstring

And Make sure that the Model of Data Access uses this Static connectionstring (Conn).

Result:

When the DB already exists => adding password seems to work, then I perform some queries with Data Access to get data and this works.

When the DB does not exists => creating the database works, then modifying the db with new password seems like it works, but right after, I try to insert some data with Data Access but this fails => with the message that "the file is missing or encrypted" => I have checked if the connectionstring is modifed and it is, also a new DBContext instance is created.

--------------------

Is there no simpler way?

I have seen this post:
http://www.telerik.com/forums/fluent-model-and-database-encryption

But SQLCipher seems to be 500 dollars now, I really don't want to cough this up just to encrypt my db, especially if the functionality is available in the sqlite library for .net itself.

 

Thank you for your response and reading my post!

 

... And if it's currently not possible, this is a feature request, since nhibernate seems to be able to do this, see below:

 

private ISessionFactory createSessionFactory()
{
    return Fluently.Configure()
            .Database(SQLiteConfiguration.Standard.UsingFileWithPassword(filename, password))
            .Mappings(m => m.FluentMappings.AddFromAssemblyOf<DBManager>())
            .ExposeConfiguration(this.buildSchema)
            .BuildSessionFactory();
}

private void buildSchema(Configuration config)
{
        if (filename_not_exists == true)
        {
            new SchemaExport(config).Create(false, true);
        }
}

 

[Thomas]

Hi Erdem,

Telerik DataAccess can use password protected databases when the Password is specified in the connection string. The important thing is to create the password protected database upfront, before you use the schema handler; this way, no change in the connection string is required at this stage.

Using the Telerik.DataAccess Fluent Sample NuGet, I was able to achieve the effect with:

class Program
    {
        static void Main(string[] args)
        {
            var cs = EnsureEncrpytedDatabase("c:\\database.db3", "MyPass");

            EncryptedDatabaseContext.DbConnection = cs;
            using (var ctx = new EncryptedDatabaseContext())
            {
                ctx.UpdateSchema();

                Console.WriteLine("Count={0}", ctx.Products.Count());
            }
        }

        private static string EnsureEncrpytedDatabase(string db, string password)
        {
            var cs = string.Format(@"Data Source={0};Password={1};FailIfMissing=True", db, password);
            try
            {
                using (var connection = new System.Data.SQLite.SQLiteConnection(cs))
                {
                    connection.Open();
                    //Some code
                    if (connection.State != System.Data.ConnectionState.Open)
                        throw new System.Data.DataException();
                    connection.BeginTransaction().Commit();
                    connection.Close();
                }
            }
            //if it is the first time sets the password in the database
            catch
            {
                cs = string.Format(@"Data Source={0}", db);
                using (var connection = new System.Data.SQLite.SQLiteConnection(cs))
                {
                    connection.Open();
                    //Some code
                    connection.ChangePassword(password);
                    connection.Close();
                    cs = string.Format(@"Data Source={0};Password={1};", db, password);
                }
            }
            return cs;
        }
    }

In this EnsureEncryptedDatabase method, the connection string is changed depending on the state of the database. I just needed to make the private const DbConnection string member into an internal static string member for this to work.

Regards,
Thomas
Telerik