Press "Enter" to skip to content

Category: Security

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

Kerberos And SQL Server

Published 2018-08-22 by Kevin Feasel

Kathi Kellenberger digs into Kerberos:

2. Why is Kerberos needed for SQL Server?

When NTLM is used, the client, for example a user logged into a laptop, contacts a domain controller when requesting access to a resource in the network. This resource could be an SSRS report, for example. When using NTLM, the user proves their identity to the SSRS server. Unfortunately, the SSRS server cannot forward the credentials of the user along to the database server. The database server will deny the request, and the end user will see an error message. This is common with SSRS but will also be seen whenever resources are needed involving multiple servers.

When Kerberos is property configured, the SSRS server can pass along confirmation of the identity of the requester to the database server via the ticket. If the login of the original requester has permission to select the data, it’s returned to the SSRS server, and the report is delivered.

Even if you are not using SSRS, you can run into issues when Kerberos is not configured properly. For example, you will often see error messages when trying to connect to SQL Server using SSMS (SQL Server Management Studio) when logged into another server when SPNs are misconfigured.

Having a good understanding of Kerberos can save you configuration headaches when going between servers.

Comments closed

Database Ownership Chaining On Azure SQL Managed Instances

Published 2018-08-20 by Kevin Feasel

Jovan Popovic shows that you can enable database ownership chaining on Azure SQL Managed Instances:

If you have the same owner on several objects in several databases, and you have some stored procedure that access these objects, you don’t need to GRANT access permission to every object that the procedure needs to access. If the procedure and the objects have the same owner, you can to GRANT permission on the procedure and Database Engine will allow the procedure to access all other objects that share the same owner.

In this example, I will create two databases that have the same owner and a login that will be used to access the data. One database will have some table and other database will have a stored procedure that reads data from the table in other database. Login will be granted to execute the stored procedure, but not to read data from the table:

“Can” and “should” here probably have different answers.  Far better to set up certificates for granting rights.

Comments closed

When MS_SSISServerCleanupJobLogin Is Orphaned

Published 2018-08-16 by Kevin Feasel

Sreekanth Bandarla noticed a problem in cleaning up SSIS metadata:

Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), well that’s not right. There should be SSIS Server maintenance job which SQL server creates to purge older entries based on the retention settings right? My immediate action was to check the retention period set and what’s the status of the job.  As I suspected, the job was failing (looks like this has been failing since ages) with below error.

The job failed.  The Job was invoked by Schedule 9 (SSISDB Scheduler).  The last step to run was step 1 (SSIS Server Operation Records Maintenance).
Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’

Read on for the root cause and solution.

Comments closed

The Key Hierarchy And SQL Server Encryption

Published 2018-08-10 by Kevin Feasel

David Fowler walks us through the various keys used in encrypting data in SQL Server:

I’m sure that we all know that SQL Server includes all sorts of interesting functionality to allow us to encrypt our data and like with all encryption techniques, that data is encrypted using keys.

In SQL Server we’ve got a number of different keys, we’ve got the Service Master Key, Database Master Keys, Symmetric Keys, Asymmetric Keys and Certificates.  These keys can be used to encrypt data but they can also be used to encrypt other keys and this is where the key hierarchy comes in.

Warning:  it’s turtles all the way down.

Comments closed