LocalDB v14 creates wrong path for mdf files

Question

Recently, I upgraded LocalDB from version 13 to 14 using the SQL Server Express installer and this instruction. After the installation, I stopped the existing default instance (MSSQLLOCALDB) of version 13 and created a new one, which automatically used the v14.0.1000 server engine.

I often use LocalDB for Database Integration tests, i.e. in my xunit tests, I create a (temporary) database which is deleted when the test finishes. Since the new version, unfortunately all my tests fail because of the following error message:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\kepflDBd0811493e18b46febf980ffb8029482a.mdf'

The odd thing is that the target path for the mdf file is incorrect, a backslash is missing between C:\Users\kepfl and DBd0811493e18b46febf980ffb8029482a.mdf (which is the random database name for a single test). The databases are created via the simple command CREATE DATABASE [databaseName] - nothing special here.

In SSMS, I see that the target locations for data, log, and backup are the following:

However, when I try to update the location, I get another error message:

How can I update the default locations so that LocalDB is able to create databases again? It's obvious that LocalDB does not correctly combine the default location directory and the database file name - is there a registry entry that I can edit? Or anything else?

Update after Doug's answer and sepupic's comment

According to this Stackoverflow question, the default location's should also be changeable via the registry. However, if I try to find the corresponding keys "DefaultData", "DefaultLog" and "BackupDirectory", I cannot find them in my registry. Did SQL Server v14 rename these registry keys, or moved these information out of the registry?

Answer 1

UPDATE

As of CU 6 for SQL Server 2017, this bug has been fixed. It is now possible to execute the following successfully:

CREATE DATABASE [CreateDatabaseTest];
DROP DATABASE [CreateDatabaseTest];

The problem, and the fact that it is fixed in CU6, is documented in the following KB article:
FIX: "Access is denied" error when you try to create a database in SQL Server 2017 Express LocalDB

To get the Cumulative Update, please go to the following page and grab the top (i.e. latest) build, which might be newer than CU6 depending on when you see this:

SQL Server 2017 build versions

---

BELOW INFO OBSOLETE AS OF SQL SERVER 2017 CU6 (Released 2018-04-17)

The lack of a backslash in the combined Path + File name appears to be a bug with SQL Server 2017. I just ran into it myself. I even tried editing the Registry to add a DefaultData string Value for C:\Users\MyAccountName\ in both of the following Keys (the 3 default paths are not in any of the LocalDB registry keys that I looked through):

• Computer\HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\UserInstances\{some-GUID-value}
• Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14E.LOCALDB\MSSQLServer

And yes, I did shutdown and start up again the LocalDB instance in both attempts.

However, I am not convinced that not being able to change the default paths is a bug as it might just be poor documentation and poor error handling combined. I say this because I just tried editing the default locations for SQL Server LocalDB versions 2014, 2016, and 2017, and all resulted in the exact same error, which in itself is odd due to being from RegCreateKeyEx(), which should be dealing with the Registry and not the file system.

Not being able to change the path is unfortunate due to the lack of backslash when creating a new Database without specifying the files to use. However, I was able to create a new Database using the full CREATE DATABASE syntax as follows:

CREATE DATABASE [XXXXX]
 CONTAINMENT = NONE
 ON PRIMARY 
( NAME = N'XXXXX_sys', FILENAME = N'C:\Users\MyAccountName\XXXXX_sys.mdf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
 FILEGROUP [Tables] DEFAULT
( NAME = N'XXXXX_data', FILENAME = N'C:\Users\MyAccountName\XXXXX_data.ndf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'XXXXX_log', FILENAME = N'C:\Users\MyAccountName\XXXXX_log.ldf',
  SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 COLLATE Latin1_General_100_CS_AS_KS_WS_SC;
GO

Answer 2

I ran into the same issue and found a workaround that shouldn't have any clear drawbacks (if I'm forgetting something, please correct me). It's based on Solomons answer, but without the need to specify the absolute path to the database files directly.

DECLARE @databaseName NVARCHAR(MAX) = 'MyDatabase'

DECLARE @dataFilePath NVARCHAR(MAX) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR) 
    + FORMATMESSAGE('\%s.mdf', @databaseName)

DECLARE @sql NVARCHAR(MAX) = FORMATMESSAGE(
    'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = ''%s'' )', 
    quotename(@databaseName), quotename(@databaseName), @dataFilePath
)

EXEC (@sql)

It uses dynamic sql and is not exactly pretty, but it gets the job done until there is an official fix to the issue.

Answer 3

I'm facing this issue too. The only workaround I found would be to grant the write access to c:\Users\ to Everyone (or something like that) and let it create the mdf files wherever it wants.

Answer 4

Thank you for your concise explanation of this problem. I ran into this same issue yesterday. I still haven't found a permanent solution, but here is my current workaround.

I'm using the Database.EnsureCreated() function to create the database.

Set connection string to include 'AttachDBFilename=' setting.

Server=(LocalDB)\\MSSQLLocalDB;Database=ExploreCalifornia;AttachDbFilename=.\\ExploreCalifornia.mdf;Trusted_Connection=True;MultipleActiveResultSets=true

Run the application. It will generate an error:

Cannot attach the file '.\ExploreCalifornia.mdf' as database 'ExploreCalifornia'.

but it will create the database.

After that, change the connection string and remove 'AttachDBFilename='.

 Server=(localdb)\\MSSQLLocalDB;Database=ExploreCalifornia;Trusted_Connection=True;MultipleActiveResultSets=true

I ran the application again with no errors and tables were created.