Press "Enter" to skip to content

Category: Security

Synchronizing User Logins Across SQL Server Instances

Published 2018-04-18 by Kevin Feasel

Hamish Watson shows how easy it is to synchronize SQL authenticated logins using dbatools:

When building new servers the most important thing after restoring and securing the database is syncing up the users. This is especially important for Availability Groups as SQL Authenticated users required the SIDS to be the same.

In the past I had some very long winded code that would do the sync – it was a mixture of TSQL and PowerShell. It worked but you know – it was cumbersome.

Read on to see how life gets easier with dbatools.

Comments closed

SQL Server And STIGs

Published 2018-04-17 by Kevin Feasel

Mohammad Darab has a quick summary of the Department of Defense’s STIG overview for SQL Server 2016:

To make it easier for people in charge of “STIG’ing” their SQL Server 2016 environment, this blog is aimed to go over the newest MS SQL Server 2016 STIG Overview document (Version 1, Release 1) that was released on 09 March 2018. If you want to read through the whole document you can download it here. Otherwise, below is my summation of the relevant sections.

This overview document was developed by both Microsoft and DISA for the Department of Defense.

The entire overview document is 9 pages (including title page, etc.)

Click through for Mohammad’s summary.  Also check out Chris Bell’s sp_woxcompliant.

Comments closed

Retrieving Server And Database Permissions

Published 2018-04-12 by Kevin Feasel

Kenneth Fisher wants you to know about your SQL Server’s permission setup:

Our host for T-SQL Tuesday this month is Jens Vestergaard (b/t) and he has asked about our favorite SSMS tool. My initial thought was to talk about using solutions in SSMSbut I’d already written about that. My next thought was to write about sp_DBPermissions and sp_SrvPermissionswhich of course I’ve written about several times. No big surprise, I wrote them after all.

So what tool am I going to write about? Well, sp_DBPermissions and sp_SrvPermissions of course. I mean I did write them after all, and Jens did say we could brag about something we wrote :).

Read on for more information about these useful tools.

Comments closed

Collecting Login Details With dbatools

Published 2018-04-11 by Kevin Feasel

Chrissy LeMaire shows us several ways to track who has connected to your SQL Server instance:

Using the default trace is pretty lightweight and backwards compatible. While I generally try to avoid traces, I like this method because it doesn’t require remote access, it works on older SQL instances, it’s accurate and reading from the trace isn’t as CPU-intensive as it would be with an Extended Event.

Click through for details on this as well as three other methods, along with the dbatools glue to make it work.

Comments closed

Encrypt Those Database Backups

Published 2018-04-05 by Kevin Feasel

David Fowler shows how easy it is to encrypt database backups in SQL Server:

We all go to great lengths to make sure that our databases are secure (or at least I really hope that we do), we make sure that only authorised users have access and then only to the databases and levels that they need.  We’re careful that all our passwords conform to crazy complexity rules.  We’re a pretty security minded lot aren’t we?

But what about our backups?  Most of the time they’re just sitting on, either a local disk on the server itself or some share somewhere.  Wherever they are, what happens if someone manages to get hold of our backup files?  They can easily just restore them onto a server where they have permissions and voila, all our super secret data is now theirs.

They could even just open the files with a hex editor read the data, it’s really not a difficult thing to do.

By default, SQL Server makes absolutely no effort to encrypt or in any way protect our backups.  That’s pretty bad in my mind.

If you’re on Standard Edition, this became available in 2014.  For Enterprise Edition, it was available earlier.  At this point, I don’t think there are good reasons for not encrypting backups on production systems.

Comments closed

Second-Order SQL Injection Attacks

Published 2018-03-28 by Kevin Feasel

Bert Wagner explains what he calls second-order SQL injection attacks:

SQL injection attacks that delay execution until a secondary query are known as “second order”.

This means a malicious user can inject a query fragment into a query (that’s not necessarily vulnerable to injection), and then have that injected SQL execute in a second query that is vulnerable to SQL injection.

Let’s look at an example.

Another way of thinking about this is a persisted SQL injection attack, akin to reflected versus persisted cross-site scripting attacks.  The fix is, don’t trust unsanitized user input.  Just because you put a user’s data into your database doesn’t mean that someone sanitized it, so treat that stuff as unsafe unless you know otherwise.

Comments closed

The Risk Of Opening Your SQL Server To The Internet

Published 2018-03-26 by Kevin Feasel

Max Vernon explains why it’s a bad idea to let your SQL Server be Internet-accessible:

First, it might be helpful to understand why someone might want to connect to their SQL Server via the Internet. Consider any of the following likely scenarios:

  • There is a client application or website hosted in a different data-center that needs access to the data stored in the SQL Server.
  • You need to provide geographical redundancy between several SQL Servers located in different countries that are only connected via the Internet.
  • You’re doing development on a hosted SQL Server and don’t understand the implications of having SQL Server exposed.

The typical way you might expose your SQ Server to the Internet when the server is behind a router or firewall is to open an incoming TCP port at the router or firewall directed at the SQL Server on port 1433. If you do this, you will very quickly start seeing incoming connection attempts from computers on the Internet. These attempts will try to login, typically via the “sa” account, continuously trying passwords.

It’s best to avoid this mess altogether, though if you do need to expose your SQL Server to the Internet, Max has tips on how to mitigate some of that risk.

Comments closed

Sharing Power BI Content Via E-Mail

Published 2018-03-23 by Kevin Feasel

Steve Hughes looks at the security implications of being able to share Power BI reports through e-mail:

My account does not have Power BI Pro, but now I can try it for free for 60 days and get access to the data while I am on the trial. I clicked both options, because I can. The Upgrade account option would require me to pay for Pro. However, Try Pro for free works and I was able to access the report fully. I have successfully shared my corporate content with a personal user.

Steve shows us where you can go to disable this if you want, as well as places where you can see what content has been shared.

Comments closed

Finding Where Power BI Local Credentials Get Stored

Published 2018-03-23 by Kevin Feasel

Eugene Meidinger hunts down where those local Power BI credentials live:

With SSIS, you have to be careful to export the SSIS files without any sensitive information included. But what about Power BI? If you save the .PBIX files on OneDrive, can you be exposing yourself to a security risk?

Looking at things, it looks like credentials for data sources are stored globally, so one wouldn’t expect them to be in the .pbix files.

Read on as he does some more sleuthing and discovers the answer.

Comments closed

Remote DAC And Vulnerability Assessments

Published 2018-03-21 by Kevin Feasel

Max Vernon points out a SQL Server Management Studio Vulnerability Assessment check which seems somewhat incomplete:

Certainly, you’d want to ensure the port for the DAC is not available to the Internet, but hopefully if you’re reading this blog you already know how silly it would be to open SQL Server to the Internet.

Assuming you don’t have the port open to the Internet, it’s very likely the DAC will not be of any use at all if you disable Remote Admin Connections as advised in the Vulnerability Assessment. My advice is to ignore this warning completely and configure the DAC to allow remote connections. Microsoft Technet has documentation about using the DAC, and says to configure it for remote connections by logging onto the server locally first, then configuring SQL Server to allow remote DAC connections, which seems a bit like putting the cart before the horse.

Read the whole thing.  I agree with Max’s assessment that if there are some basic controls around your instance (like not letting SQL Server be Internet-accessible, putting SQL Server instances in a protected subnet, etc.), remote DAC is definitely useful enough to keep running.

Comments closed