windows authentication

Set up access to network shares from SQL Server

/ Daniel Hutmacher / 4 Comments

Using a local service account for your SQL Server service, your server won’t automatically have permissions to access to other network resources like UNC paths. Most commonly, this is needed to be able to perform backups directly to a network share.

Using a domain account as your SQL Server service account will allow the server to access a network share on the same domain, but if the network share is not on your domain, like an Azure File Share, you need a different solution.

There’s a relatively easy way to make all of this work, though.

Continue reading →

Connecting a SQL Server client on Linux using Active Directory authentication

/ Daniel Hutmacher / 3 Comments

or: How I learned to stop worrying, and love all-caps domain names.

I’m a complete beginner at Linux, so I should preface this post with the fact that these are my humble notes after hours of pulling my hair. It’s not really a fully-fledged how-to article, and there are lot of things I’m not covering. But I figured it may help someone out there at some point.

Continue reading →

Detaching a database also alters file permissions

/ Daniel Hutmacher / 4 Comments

Moving a database or some of its files from one drive to another or from one instance of SQL Server to another is as simple as detaching it and re-attaching it again. This is actually pretty smart, compared to backup–restore, because you only perform one I/O operation (moving the file), as opposed to two (backing up, restoring).

But when you try to attach the database, you might get something like

Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf".
Operating system error 5: "5(Access is denied.)".

The reason, as I found out the hard way, is that SQL Server can actually modify the file permissions of the .mdf and .ldf files when it detaches a database.

Continue reading →