How to open a password protected SQL Server CE database with Entity Framework

Question

I am using EF 6.0 and SQL Server CE 4.0. The .sdf file is password protected, which I verified by opening the file with LinqPad. When I try to open this database in code with the following connection string, I get an exception:

The specified password does not match the database password

Code:

using (var context = new MyDbContext("ExamManagement"))
{
    context.Database.Initialize(false);
}

Connection string:

<connectionStrings>
    <add name="ExamManagement" 
         connectionString="Data Source=|DataDirectory|Pikeman.sdf;Max Database Size=4091;Password=123;" 
         providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

Stack trace:

at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass7.b__5()
at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext()
at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)

please see my updated screen shot. It seems that the connection string is correct but the Password part is ignored. — Frank Liu
– Frank Liu, Commented Apr 3, 2017 at 12:26
Are you testing in linqpad against the correct database file? Look in your bin/debug folder - maybe there is a copy of the sdf file there! — ErikEJ
– ErikEJ, Commented Apr 3, 2017 at 12:35
The connection string is pointing to the right sdf file. If I remove the .sdf from the target directory, executing the code above will create a new .sdf file at the target directory without any exception. However, the newly created database is NOT password protected. Again, it seems that the Password part is ignored. — Frank Liu
– Frank Liu, Commented Apr 3, 2017 at 23:17
To see if the issue is EF related, can you try opening ADO.NET connection with the same connection sting from the same code location that is generating the exception. e.g. context.Database.Connection.Open(); or new SqlCeConnection("Data Source=|DataDirectory|Pikeman.sdf;Max Database Size=4091;Password=123;").Open(); and let us know. — Ivan Stoev
– Ivan Stoev, Commented Apr 12, 2017 at 8:03

bubi · Accepted Answer · 2017-04-13 17:47:59Z

1

+50

The connection string is OK (usually I don't specify Max Database Size, you can try to delete the parameter but I'm quite sure this is not the problem).
So, in your case, I think you are probably opening a database with a different password (as the exception suggests) or you are opening a wrong database. Try to specify an absolute path and open the database from that path, for example

<connectionStrings>
    <add name="ExamManagement" 
         connectionString="Data Source=C:\temp\Pikeman.sdf;Password=123;" 
         providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

answered Apr 13, 2017 at 17:47

bubi

6,5113 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frank Liu Over a year ago

Ok. Found the issue. The problem was the the default DbContext constructor was using a default connection string which is different from the one in the app.config file. When the DbMigrationsConfiguration seeds the database, it uses the default connection string without the password, which happens to pointing to the same directory as the app.config connection string.

Collectives™ on Stack Overflow

How to open a password protected SQL Server CE database with Entity Framework

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related