Press "Enter" to skip to content

Category: Security

Basic Linux For The SQL DBA

Published 2018-09-28 by Kevin Feasel

Kellyn Pot’Vin-Gorman continues her series on getting SQL Server DBAs ramped up on Linux:

Let’s begin with discussing WHY it’s not a good idea to be root on a Linux host unless absolutely necessary to perform a specific task. Ask any DBA for DB Owner or SA privileges, and you will most likely receive an absolute “No” for the response. DBAs need to have the same respect for the host their database runs on. Windows hosts have significantly hardened user security by introducing enhancements and unique application users to enforce similar standards at the enterprise server level, and Linux has always been this way. To be perfectly blunt, the Docker image with SQL Server running as root is a choice that shows lacking investigation to what privileges are REQUIRED to run, manage and support an enterprise database. This is not how we’d want to implement it for customer use.

Unlike a Windows OS, the Linux kernel is exposed to the OS layer. There isn’t a registry that requires a reboot or has a safety mechanism to refuse deletion or write to files secured by the registry or library files. Linux ASSUMED if you are root or if you have permissions to a file/directory, you KNOW what you’re doing. Due to this, it’s even more important to have the least amount of privileges to perform any task required.

Proper deployment would have a unique MSSQL Linux login owning the SQL Server installation and a DBAGroup as the group vs. the current configuration of ROOT:ROOT owning everything. With all the enhancements to security, this is one area that as DBAs, we should request to have adhered to. Our databases should run as a unique user owning the bin files and database processes.

Much of this post is walking us through some basics of security, but it also includes helpful built-in commands unrelated to security, like df to view free disk space.

Comments closed

Always Encrypted With Secure Enclaves In SQL Server 2019

Published 2018-09-26 by Kevin Feasel

Jakub Szymaszek walks us through Virtualization Based Security memory enclaves in Windows Server 2019 and SQL Server 2019:

Today, we are super excited to announce that you can now try and evaluate Always Encrypted with secure enclaves in the preview of SQL Server 2019.

Always Encrypted with secure enclaves in SQL Server 2019 preview uses an enclave technology called Virtualization Based Security (VBS) memory enclaves in the upcoming version of Windows (Windows Server 2019 and Windows 10, version 1809), which is currently also in preview. A VBS enclave is an isolated region of memory within the address space of a user-mode process. The isolation of VBS enclaves is provided by the Windows hypervisor, which makes VBS enclaves appear as black boxes, not only to the processes containing them, but also all other processes and the Windows OS on the machine. Even machine administrators are not able to see the memory of the enclave. The below screenshot shows what an admin would get to see when browsing the enclave memory using a debugger (note the question marks, as opposed to the actual memory content).

The compliance regime is shifting toward preventing high-privilege users (DBAs, sysadmins, etc.) from accidentally or maliciously exposing sensitive information, so it makes sense that this is the primary security push.  I think that these changes are starting to make Always Encrypted a better option than a roll-your-own data encryption model.

Comments closed

What’s In SQL Server 2019 CTP 2.0?

Published 2018-09-25 by Kevin Feasel

Aaron Bertrand gives us the highlights:

  • Certificate Management in Config Manager View and validate all of your certificates from a single interface, and manage and deploy certificate changes across all of the replicas in an Availability Group or all of the nodes in a Failover Cluster Instance.

  • Built-in data classification A new ADD SENSITIVITY CLASSIFICATION statement helps you identify and automatically audit sensitive data, a huge step up from the previous SSMS wizard (which just used extended properties).

Aaron also digs into the engine a bit:

APPROX_COUNT_DISTINCT

This new aggregate function is designed for data warehouse scenarios, and is an equivalent for COUNT(DISTINCT()). Instead of performing expensive distinct sort operations to determine actual counts, it relies instead on statistics to get something relatively accurate. You should find that the margin of error is within 2% of the precise count, 97% of the time, which is usually fine for high-level analytics, values that populate a dashboard, or quick estimates.

On my system I created a table with integer columns ranging from 100 to 1,000,000 unique values, and string columns ranging from 100 to 100,000 unique values. There were no indexes other than a clustered primary key on the leading integer column. Here are the results of COUNT(DISTINCT()) vs. APPROX_COUNT_DISTINCT() against those columns, so you can see where it is off by a bit (but always well within 2%):

By the way, APPROX_COUNT_DISTINCT() is a really good idea, and I’m glad it’s here.

Comments closed

Permissions Needed To View Permissions

Published 2018-09-19 by Kevin Feasel

Kenneth Fisher shows us what we can do to grant a low-privilege user the ability to view permissions for other users:

Every now and again you’ll have a user that needs to be able to see what permissions other users have. Not change them, just look at them. In the cases I’ve seen it’s usually a manager or something similar reviewing the database permissions. Or maybe someone doing an entitlement review (checking to make sure everyone has the permissions they need, and just the permissions they need).

And, if you’ve made it this far, you read the first line and you know that the permission required is VIEW DEFINITION. What’s interesting is that this permission is usually used to grant someone the ability to look at the code behind T-SQL code objects. SPs, Views, Functions etc. But, it turns out that principals also have the VIEW DEFINITION permission.

Kenneth has a few notes for this as well, so check it out.

Comments closed

Limiting Azure Administrator Data Access

Published 2018-09-17 by Kevin Feasel

Melissa Coates gives us a look at one aspect of Azure security:

Recently a customer expressed concern that an owner of an Azure resource group automatically gains access to the data within the services contained in the resource group. In this case, the customer was specifically referring to data in Azure Data Lake Storage Gen 1 but this concept applies to Azure Storage and other data-oriented services in Azure as well. The customer’s comment prompted me to look into available alternatives. This is by no means a detailed security post…rather, I’m trying to share a few nuggets of what I learned.

Worth the read.  Much of the latest round of regulatory push seems to be in the realm of limiting high-access insiders (like DBAs) from accessing sensitive information, and this post aligns with that.

Comments closed

SQL On Linux AD: Group Membership Issues

Published 2018-09-07 by Kevin Feasel

Dylan Gray and Tejas Shah continue their series on troubleshooting issues when connecting to SQL Server on Linux via an Active Directory account:

Imagine a scenario where after a successful AD login and running a couple queries, some users may see the error “Could not obtain information about Windows NT group/user ‘CONTOSO\user’.” This is due to a failure when searching for group memberships for the logged in user and can be easily fixed.

When a user logs in, their group memberships are looked up and used to determine if they have the privileges to login. Once the user is connected SQL Server must validate their group memberships in many scenarios, to make sure their effective access permissions have not changed. For example, if user CONTOSO\user1 was a member of CONTOSO\group1, and CONTOSO\group1 has login permission for the SQL Server instance, then CONTOSO\user1 can login successfully. However, if after CONTOSO\user1 logs in, they are removed from CONTOSO\group1 by a domain admin, then their access to SQL Server should be revoked.

Click through to see what SQL Server on Linux uses to check AD group information and what you can do if there’s a problem.

Comments closed

Encrypting SQL Server Connections

Published 2018-09-06 by Kevin Feasel

Jamie Wick has a great post showing how you can encrypt connections to SQL Server:

So, a question that should be asked is: How secure are your client connections? Here are a couple common misconceptions about SQL server client connections.

Misconception: Usernames & passwords (SQL or Windows) are used to connect to SQL server databases, which means the client-server connection is secure.

Explanation
Usernames & passwords are used to control who has what level of permission (read/write/modify) to the data & database. By default, the information being transmitted is not encrypted. As John Martin shows in this article, it is relatively easy for someone with access to a network (wireless access point or LAN connection) to read the unencrypted data that is being sent between a SQL server and client.

Definitely read the whole thing.  We’re at a point where the overhead cost of encrypting connections is low enough that there’s not much reason to leave production servers transmitting openly over the wire.

Comments closed

SQL On Linux: Common Active Directory Login Issues

Published 2018-09-06 by Kevin Feasel

Dylan Gray and Tejas Shah continue their troubleshooting series for SQL Server on Linux integrations with Active Directory:

1. When a user performs an AD connection, internally the user connects to a service principal name (SPN). The SPNs are in the form “MSSQLSvc/host.contoso.com:1433”, and they must be registered when setting up AD logins for SQL Server on Linux. When a client app requests a connection (e.g. sqlcmd), it takes the server users wish to connect to, prepends “MSSQLSvc/” and appends “:**<port>**”, and this is the SPN which the connection attempts to authenticate with.

So, if user connects with “sqlcmd -E -S host.contoso.com”, it authenticates with the SPN “MSSQLSvc/host.contoso.com:1433”, and everything succeeds. If user connects with “sqlcmd -E -S host”, it authenticates with “MSSQLSvc/host:1433”. If the SPN the client is authenticating with does not exist, the connection will fail. So, if the SPNs in the mssql.keytab are only “MSSQLSvc/host.contoso.com:1433”, users can only connect to “host.contoso.com”, not “host” and not to the IP. If user needs to be able to connect with variations of host name and IP address, then all appropriate SPNs should be created and configured in the mssql.keytab file.

Read on for more common issues and their solutions.

Comments closed

Joining Your SQL Server On Linux VM To A Domain

Published 2018-09-05 by Kevin Feasel

Dylan Gray and Tejas Shah provides some tips on joining a SQL Server on Linux instance to an existing Active Directory domain:

AD authentication is a popular mechanism for login and user authentication. It works very well in many scenarios, especially for enterprise applications. AD authentication is a supported scenario on SQL Server on Linux. Configuring the Linux VM to join with Active Directory (AD) can be a little tricky at sometimes though, especially in a complex enterprise environment.

  • One error message you may see from “realm join” is “realm: Couldn’t join realm: This computer’s host name is not set correctly.” This is due to a generic hostname (e.g. “localhost”), an incorrect domain in your hostname (e.g. “host1.abcd.com” instead of “host1.contoso.com”), or a duplicate hostname on the domain. To fix this, edit /etc/hostname to have a unique hostname and reboot the machine. On Ubuntu, it can also be helpful to put the fully qualified domain name in /etc/hostname (e.g. “host1.contoso.com” instead of “host1”).

 

They provide in this post some of the low-hanging fruit answers, where the problem is in basic server configuration.

Comments closed

Finding A Schema’s Owner

Published 2018-08-24 by Kevin Feasel

Jack Vamvas shows how to find out which user owns a particular schema in a database:

Question: How can I find the owner of a SQL Server schema ?   I want o find the owner through a t-sql solultion , rather than looking through the GUI.

Answer: To find a schema owner you can use either sys.schema view or the information_schema.schemata. Since SQL 2005,  information_schema.schemata has come into line with sys.schema.     information_schema.schemata returns schemas just from the current database.

Click through for simple examples of both methods.

Comments closed