40

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:

LocalDB target locations

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

Error message when trying to update

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?

2
  • FYI, I also cannot update the Database default locations when running SSMS in admin mode.
    – feO2x
    Nov 21, 2017 at 11:59
  • 1
    Please see the update in my answer. This bug has been fixed as of CU6, released in mid-April.. May 4, 2018 at 17:55

4 Answers 4

30

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
2
  • We're taking the approach in our database setup that if it's a LocalDb connection, that we specify the path of the MDF file, just like in your workaround. But it does not seem necessary to also specify the name of the LDF or LOG ON section of the CREATE DATABASE statement. The LDF files seem to get created, by default, in the same location as the MDF file. (Our use case of LocalDb is mainly for use in automated tests.)
    – tgharold
    Dec 28, 2017 at 20:28
  • @tgharold Please see the update at the top of my answer. This bug was very recently fixed in the new CU6 patch :-). May 8, 2018 at 15:23
5

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.

2
  • 1
    Interesting. It might be slightly better to move the 2nd QUOTENAME to the 2nd param of FORMATMESSAGE and put double quotes around the file path: FORMATMESSAGE(N'CREATE DATABASE %s ON PRIMARY ( NAME = %s, FILENAME = "%s" )', QUOTENAME(@databaseName), QUOTENAME(@databaseName), @dataFilePath);. But it seems to work as you have it now, so +1 for this approach. There is still a case for either hard-coding the name or passing in a path: when you don't want to use the USERPROFILE root. But you could allow for that here and just default to InstanceDefaultDataPath if @Path IS NULL. :-) Mar 7, 2018 at 22:33
  • Thank you. I've edited the answer with your suggestion. I agree hard-coding the absolute path is definitely still a valid solution. In our scenario though, we only recreate the database during automatic testing and I had to make sure it'd work for all my colleagues and build server, without having to coordinate everyone creating a folder at the exact same path on their local machine. :-) Mar 8, 2018 at 5:56
4

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.

4
  • 1
    Thanks, this solved it for me as well! This workaround sucks, but it's only on our local build server, so I don't care about the additional user rights. Mar 21, 2018 at 15:15
  • @HannesSachsenhofer: I agree, it sucks. But I was promised by the LocalDB dev team they'll fix this bug in the upcoming hot-fix release. Mar 21, 2018 at 19:52
  • 1
    Grant write access to everyone seems like a pretty bad workaround to me
    – Raphael
    May 8, 2018 at 15:17
  • @Raphael: Why? You have multiple users on you dev box? And whom you don't trust? May 8, 2018 at 21:08
3

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.

4
  • A couple of questions: First, just to be sure - you were getting an (Access is denied) error initially, and this resolved that, correct? Second - SQL Server Management Studio is the only application mentioned by the IP, and this doesn't sound like something you did from there - what application did you do all this from?
    – RDFozz
    Nov 21, 2017 at 21:30
  • Thanks for your answer, but this does not really solve my problem. My test code creates a new database with a simple CREATE DATABASE [databasename] against LocalDB and this very statement is causing problems as LocalDB wrongly concatenates the default locations directory with the randomly generated database name (see paragraph 3 and 4 of my question). I need a way to fix the default locations so that this concatenation problem does not occur.
    – feO2x
    Nov 22, 2017 at 8:35
  • Currently, I'm not able to create a database via SQL / DDL at all. It doesn't matter if I run the statement via SSMS, or from code, or anywhere else, because LocalDB always tries to create the database directly in the Users directory (which is completely wrong, no files are allowed to exist in this directory).
    – feO2x
    Nov 22, 2017 at 8:52
  • 1
    You've possibly misspelled AttachDBFilename.
    – tgharold
    Dec 27, 2017 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.