Set up access to network shares from SQL Server

2021-09-02 / 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 →

Connect using Windows authentication across domains

2021-02-052021-02-05 / Daniel Hutmacher / 4 Comments

You’re a consultant or remote worker, and you’re connecting into your work or client network using a VPN. But when you try to connect to the SQL Server, you get this.

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. (Microsoft SQL Server, Error: 18452)

There are a few simple workarounds, each with their own pros and cons.

Continue reading →

An alternative to data masking

2018-02-052018-02-02 / Daniel Hutmacher / 1 Comment

Dynamic data masking is a neat new feature in recent SQL Server versions that allows you to protect sensitive information from non-privileged users by masking it. But using a brute-force guessing attack, even a non-privileged user can guess the contents of a masked column. And if you’re on SQL Server 2014 or earlier, you won’t have the option of using data masking at all.

Read on to see how you can bypass dynamic data masking, and for an alternative approach that uses SQL Server column-level security instead.

Continue reading →

Encrypting SQL Server connections with Let’s Encrypt certificates

2017-11-22 / Daniel Hutmacher / 9 Comments

Encrypting your SQL Server’s TDS connections should be high on your list of things to do if you’re concerned with the privacy of your data. This often boils down to one big problem: can you get a valid certificate without paying a ton of money, and will it work with SQL Server?

So follow me down the rabbit hole, as we work out the steps to using Let’s Encrypt to create (and auto-renew!) a certificate for SQL Server. This is going to get technical.

Continue reading →

Legacy apps that don’t believe in schemas

2017-10-132017-10-12 / Daniel Hutmacher / 3 Comments

Inspired by an actual customer scenario: what if you have a legacy app that doesn’t schema-prefix its database objects, but you want it to work with a specific assigned schema? There’s a quick and easy solution.

Continue reading →

Effective permissions on SQL Server

2017-09-272017-09-26 / Daniel Hutmacher / 12 Comments

SQL Server Management Studio allows you to view effective permissions on an object, but it’s limited in a few important respects. To work around some of those limitations, I’ve built a stored procedure to display all the defined and effective permissions across an entire SQL Server database.

Continue reading →

Availability Groups: How to sync logins between replicas

2016-10-112020-05-16 / Daniel Hutmacher / 76 Comments

AlwaysOn Availability Groups are a reasonably simple way to set up disaster recovery (DR) for your SQL Server environment, and with fairly little effort, you can get a bit of high availability (HA) from it as well. But there are a few gotchas, the most obvious of them being that Availability Groups only synchronize specific user-databases, not the entire server setup.

Things that are not included in AGs include logins, SQL Server Agent jobs, SSIS packages stored in SQL Server, linked servers and server settings. You could sync these manually (as is often the case), but wouldn’t you just love to have an automated process do all this for you?

In this post, we’ll look at logins. For the sake of simplicity, I’ll assume that you have a primary replica with a single AG and any number of secondary replicas. The logic holds true if you have multiple AGs, it just gets trickier.

Continue reading →

Detaching a database also alters file permissions

2016-05-112016-05-10 / 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 →

The SQL Server security model, part 4: execution context

2014-08-102014-08-04 / Daniel Hutmacher / 6 Comments

This is the fourth installment in this series on SQL Server security. Today, we’re going to take a closer look at some key security concepts concerning object ownership and ownership chaining as well as execution context and impersonation in SQL Server. This could get technical.

Continue reading →

The SQL Server security model, part 3: permissions

2014-08-032017-09-26 / Daniel Hutmacher / 4 Comments

This is the third installment in a series on assigning SQL Server permissions. In the previous posts, we’ve looked at security principals (the users and groups that carry the permissions) as well as the securables (the objects you want to control access to). Building on this understanding, we’re getting ready to look at the fabric that connects principals with securables: Permissions.

Continue reading →