SQLCLR in SQL Server 2017

Published On: 2017-04-19By:

Security of your databases has become the most important part of your job as a DBA. No one wants to be the next company to leak a list of customers, leak photos of children and their parents using your company’s product, or have a key marketing database released. Security is also a very complicated, multi-layered thing. There’s not a switch you can turn on to suddenly have good security. Additionally, you can be doing all the right things, and a few bad lines of application code can subvert all of the measures you have taken to protect your data.

With this is mind, Microsoft has made some big changes to CLR in SQL Server 2017. SQL CLR has always been an interesting area of the engine—it allows for the use of .NET code in stored procedures and user defined types. For certain tasks , it’s an extremely powerful tool—things like RegEx and geo functions can be much faster in native CLR than trying to do the equivalent operation in T-SQL. It’s always been a little bit of a security risk, since under certain configurations, CLR had access to resources outside of the context of the database engine. This was protected by boundaries defined in the CLR host policy. We had SAFE, EXTERNAL_ACCESS, and UNSAFE levels that we could set. SAFE simply limited access of the assembly to internal computation and local data access. For the purposes of this post, we will skip UNSAFE and EXTERNAL_ACCESS, but it is sufficed to say, these levels allow much deeper access to the rest of the server.

Code Access Security in .NET (which is used to managed these levels) has been marked obsolete. What does this mean? The boundaries that are marked SAFE, may not be guaranteed to provide security. So “SAFE” CLR may be able to access external resources, call unmanaged code, and acquire sysadmin privileges. This is really bad.

So What Now?

Microsoft is recommending enabling “CLR Strict Security” in SQL Server 2017, which means users cannot create any CLR assemblies unless they have elevated permissions. This could be a breaking change anywhere you want to use dynamic CLR. The user needs to have CREATE ASSEMBLY and one of the following options has to be true:

  • Database has the TRUSTWORTHY property on AND the database owner (DBO) has the UNSAFE ASSEMBLY permission on the server
  • Assembly is signed with a certificate or asymmetric key that has a corresponding login with the UNSAFE ASSEMBLY permission on the server

In short, the engine treats all CLR assemblies as unsafe, even if they are created with SAFE or EXTERNAL_ACCESS permission sets.

You will need to sign your assemblies. Also, if you try to restore a database with CLR assemblies into SQL Server 2017, you may run into issues if your assemblies are unsigned.

But Now I Have to Do Work?

What does this mean for you, the SQL DBA or Developer? This is a breaking change (and breaking changes are a BIG DEAL at Microsoft—they don’t like to break things unless there are really good reasons). This means if you are using CLR, and you want to move to SQL Server 2017 (or really, let’s face it, these security risks are in lower versions of the code), you need to work out a signing system for your CLR assemblies and possibly some key management infrastructure. You need to evaluate your older CLR code to ensure that none of it is running under UNSAFE or EXTERNAL_ACCESS (unless you want to turn off “CLR Strict Security”, which you really don’t want to do). Also, if you want to run SQL Server 2017 on Linux, you will be limited to the SAFE permission set.

We Only Have Third Party Apps…

Good luck with that. This is a big change, and we all know lots of smaller independent software vendors (ISVs) have just gotten around to supporting SQL Server 2014. In most of my work with ISVs, I haven’t seen a ton of CLR in most of the vendor apps. That being said, I’m  sure it exists in many ISV apps. The angle I would take as a DBA towards my ISV(s) is to use the “audit hammer” with the vendor. If you are not familiar with this approach to managing your vendors, you can mention that your internal audit process is now failing unsigned assemblies that can’t run under CLR Strict Security. This is probably accurate anyway—CLR has been an audit flag for years. I would also recommend doing a comprehensive testing process to ensure your CLR functions as you would expect.


Correcting the Outbound Email Address for Office 365 Users When Using AD Connect

Published On: By:

That’s one hell of a long title isn’t it? I ran across a little issue in our Office 365 account recently. That problem was users having the wrong outbound email address in Office 365. I was trying to figure out why a new account that I had setup in our dcac.co domain kept showing up in Outlook at username@dcassoc.onmicrosoft.com instead of username@dcac.co.

Well the reason that it was showing up this way was because that’s how Office 365 was configured, of course. Now when I create a user (we’ve never had Exchange on prem, so we can’t see any of the Exchange tabs) I setup the account for the user, let it sync to Azure and added the licenses and didn’t think anything of it.

Later I saw the issue. Fixing it is pretty easy, if you know where to look.

Fixing it before the user is in AAD

If you are creating a new user in AD, making it so that the account is created correctly is actually pretty easy.ad1

In the properties of the user, fill out the email address field in the users account. This will tell Azure Active Directory and Office 365 that the users outbound email address should be whatever you put in that field (assuming that it’s a domain that you have control over).

If the users account has already synced to Azure as you discover this problem, this won’t fix it (or at least it didn’t for me).

In the testing that I did, using this field to fix it only works on account creation.  But that’s ok, it’s still a pretty easy fix to fix this after the user has had their account synced to Azure.

Here’s what the user account looks like in Azure after the initial sync has completed with the email field blank.

office1

Fixing it after the user is in AAD

If the user is in AAD, and Office 365’s Exchange properties shows the user as the onmicrosoft.com account, all is not lost. You don’t need to do anything drastic like deleting the user or anything.

In a domain controller, probably the one with AD Connect installed on it, open Active Directory Users and Computers.  Click on View then Advanced Features.

Now go find the problem user and open their account.  You’ll see a new tab called “Attribute Editor”, select it.  Scroll down until you find the proxyAddresses field.  It’s probably listed as “<not set>”. That’s fine, we’re going to fix this.  This proxyAddresses field is how we tell Azure and Office 365 (and Exchange) what the email addresses are that the user can receive email as, and what email address is their outbound email address.Setting outbound email address in Active Directory

If we edit that we’ll get a dialog that allows use to add items to the list. Now don’t just add in email addresses, that isn’t going to work.  You can list all sorts of items in there.  Specifically we want to add in our SMTP addresses.  We do this in the format of smtp:username@domain.com.  In the case of our test user it’ll be testuser@dcac.co.  Now we can put in as many email addresses as we need them to get email at.  So how do we set the outbound email address?  We make the “smtp” part uppercase.

If you are a company that changes users email addresses when then change their name (they get married or divorced for example) then you’ll want to keep their old email address in there.  So you just set it as “smtp:oldname@domain.com” in all lowercase.

In the screenshot we’ve two SMTP addresses, username@dcac.co with the upper case SMTP and oldname@dcac.co with the lowercase SMTP.  This allows the user to received email on both username@dcac.co and oldname@dcac.co while sending any outbound emails as username@dcac.co.

office2If I go look at the list of accounts which the user has, you’ll now see that the SMTP in upper case is the bolded one (that’s how Office 365 shows which email is the outbound).  And if we look at the user in the mailbox list we’ll now see that the email address has changed from username@dcassoc.onmicrosoft.com to username@dcac.co.

office3

A fairly easy change, and no impact to the user, other than the impact that we wanted, which is that the users email address is now correct and matches the companies branding. It’ll take a while at this point for the users email address to be corrected in everyone’s Global Address List (GAL). It’ll take a little time, the GAL doesn’t update that often, but when it gets around to updating the users will all get the update.

Using PowerShell to fix things in bulk

This change can be made in PowerShell as well. In this case I’ve setup the PowerShell to only for against a single user, but you can setup the -Filter parameter anyway you need to in order to get things working the way you need.

$users = Get-ADUser -Filter {UserPrincipalName -eq 'testuser@dcac.co'} -Properties SamAccountName, ProxyAddresses, UserPrincipalName
$users | Foreach {
Set-ADUser -Identity $_.SamAccountName -Add @{Proxyaddresses="SMTP:"+$_.UserPrincipalName} -whatif
}

I’ve left the -whatif in there so you can see what it’ll do before running it. You’ll want to be careful running PowerShell against your Active Directory domain as you can really screw things up if you aren’t careful. Be warned, if you already have values in this ProxyAddresses field, this will simply append to them. If there’s a default in there you’ll now have two and Office 365 will get cranky. So you may need more logic and checking in your script. But I’ll leave that to you.

If you are planning a cloud migration, or you have already started one, be sure to contact DCAC.  We are one of the premier experts in cloud migrations, frequently training other consulting companies on how to perform cloud migrations.

Denny

The post Correcting the Outbound Email Address for Office 365 Users When Using AD Connect appeared first on SQL Server with Mr. Denny.

Blogging at SQLPerformance.com

Published On: 2017-04-18By:

I am honored to be a Guest Blogger at SQLPerformance.com. As a member of the SentryOne Product Advisory Council (PAC) I will be writing occasionally for the site. You can catch my first blog post It’s Not You It’s Me talking about ways to reduce Disk I/O through simple changes.

SQLPerformance.com is about providing innovative and practical solutions for improving SQL Server performance. Whether you are running a 3rd party application database where very little can be changed, or you are a DBA at a site where getting the application developers to change anything is next to impossible, they cover both the “how” and the “why.”

It’s time to grow DCAC again, this time adding Monica Rathbun to the company

Published On: 2017-04-17By:

It’s been an interesting ride running DCAC and growing the company so that we can handle more projects and more clients at once.  I’m pleased to report that we’re growing the company again.  We are adding Monica Rathbun (b | t) to the company as our newest consultant.

Monica brings 16 years of experience working as a SQL Server DBA to DCAC, so needless to say, she’s a great fit.

Monica has been recognized by Microsoft as a Microsoft MVP for Data Platform (the third one at DCAC) and has spoken at a variety of community events including SQL Saturday’s all around the US.  She also runs her local SQL Server user group and is a Regional Mentor for the PASS organization.

We’re looking forward to having Monica on the team, and doing some great work together with her.

You can read Monica’s announcement on her blog here.  Like everyone else’s blogs, we’ll be syndicating Monica’s blog here so that you can see all the great posts that she’s got with the rest of the teams.

Denny

1 2 3 330

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.