The security announcements around Azure SQL Database keep coming. Auditing was implemented a few months ago, and today it was followed by a preview of row-level security. There were also announcements around transparent data encryption (TDE) and a new dynamic data masking feature in future but these aren’t available, even in preview, yet. The row-level […]
Bob Beauchemin
SQL Server Blog
Taking the Azure SQL Database row-level security preview for a spin
Posted on: January 30, 2015 3:35 pm
An implied (not implying) permissions function
Posted on: August 28, 2013 2:59 pm
I’d always wondered why they didn’t make the “ImplyingPermissions” function (code is part of the books online) part of the product. I originally thought it was because the function was subtly (or not so subtly) misnamed. What the function does, is “given a permission in a permission class, return a table of which built-in permissions […]
I’m speaking on SQL Server service accounts next week
Posted on: January 17, 2013 10:32 am
Just to let you know. The Portland (Oregon) SQL Server User Group has invited me to speak at the meeting on Thursday, Jan 24 at 6:30pm. I’ll be speaking on SQL Server service accounts, entitled “Who’s running my SQL Server services?”. I’ll be covering the evolution of service account choices and management in later versions of […]
Security Best Practices Whitepaper for SQL Server 2012 is Live.
Posted on: March 9, 2012 9:52 am
I recently completed another update to the whitepaper, now entitled "SQL Server 2012 Security Best Practices – Operational and Administrative Tasks" to cover best usage of the security features in SQL Server 2012. And, concurrently with the launch of SQL Server 2012, it was available on the web a few days ago. Many thanks to the […]
Auditing database-level objects in SQL Express 2012
Posted on: December 1, 2011 9:43 pm
… Continued from previous blog entry … The point of using a Server Audit Specification with database events in SQL Server 2012 is this. In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012, one of the new features (to quote BOL) is: "Support […]
Using filtering and server audit specs to audit DB objects in SQL Server 2012
Posted on: December 1, 2011 9:34 pm
I've always been pretty "standard" in my approach to SQL Server's auditing feature. That is, Server Audit Specifications are for auditing server-level objects and Database Audit Specifications are for auditing database-level objects. There have always been a few "Audit Action Groups" that pertain to database objects that could be specified in Server Audit Specifications. An […]
Local Windows Groups for Service Accounts (almost) gone in SQL Server 2012
Posted on: December 1, 2011 2:47 pm
When I installed CTP3 of SQL Server 2012 (on Windows Server 2008 R2 OS), I noticed that the "Service SID account" (known as the Managed Service Account) was directly available in the setup dropdown box, selected it, wrote a blog entry mentioning it, and went on. Lately, I've been looking at the local Windows groups (or […]
A row-level and label security offering for SQL Server
Posted on: July 20, 2011 1:59 pm
Two of the security features that folks have asked me about, especially since around 2002, is row-level and label security. Row-level security was present in one of the early betas (beta 1, IIRC) of SQL Server 2005, but it was pulled almost right after that beta release, never to return. Row-level security is a self-descriptive […]
Some idiosyncrasies in SQL Server service and service user group names
Posted on: May 12, 2011 1:58 pm
In doing research for the Service SID posting, I encountered some odd things about service names, service user group names and services. I said I'd write about these; this is the followup post. Most of the information about Windows Services, Service Accounts, and user groups that are created for SQL Server services (for security purposes) […]
About SQL Server’s usage of Service SIDs
Posted on: May 11, 2011 4:56 pm
A couple of weeks ago at DevDays Netherlands, I struggled a bit with a demo that was relatively straightforward in SQL Server 2008 running under Windows Server 2003 R2, but has an interesting twist in SQL Server running under Windows Server 2008. The interesting twist is based around the fact that SQL Server 2008 and above use […]
SQL Server Security Best Practices whitepaper update is live
Posted on: January 7, 2011 3:16 pm
I've recently completed an update to the SQL Server 2005 Security Best Practices whitepaper. It's available on the security and compliance website under whitepapers or the direct link is here. Unsurprisingly, its called "SQL Server 2008 R2 Security Best Practices – Operational and Administrative Tasks". Thanks to all the folks who reviewed it. Enjoy. @bobbeauch
SQL Server security precon at TechEd 2010.
Posted on: April 19, 2010 2:13 pm
This blog posting is meant to bring attention to the fact that I'm doing a preconference talk, "A Day of SQL Server Security" at TechEd 2010 in New Orleans in June. OK, the TechEd folks asked me to publicize it. I'm also doing two breakout sessions, one on "Entity Framework and LINQ2SQL vs. Stored Procedures", […]
At the user group…auditing and sys.fn_get_audit_file
Posted on: July 24, 2009 4:38 pm
I really enjoyed speaking at the Portland SQL Server User Group meeting last night about SQL Server security…and I have an update. We were talking about the supposed inability of auditing to audit usage of sys.fn_get_audit_file, the system function that reads an audit log. Raul Garcia of the SQL Server team had the answer. "For the […]
At the Portland SQL Server User Group this month
Posted on: July 8, 2009 10:58 am
This month I'll be presenting a session for the Portland SQL Server User Group. I'll be discussing and demonstrating the new security features in SQL Server 2008 with a post-talk Q&A about SQL Server security in general. I've also got some swag to raffle off. See you on the fourth Thursday!
SQL Server Compliance Portal is now live
Posted on: January 14, 2009 2:39 pm
Today I came across the new SQL Server 2008 Compliance Portal. This portal has information and links to the new Compliance whitepaper and compliance scripts (the "sample files" at the bottom of the main page on the compliance portal). New features for ensuring compliance in SQL Server 2008 include Policy-Based Management, Auditing, and TDE, to […]
My SQL Server security best practices whitepaper is available
Posted on: March 21, 2007 6:52 pm
For folks that have been asking…my latest whitepaper "SQL Server 2005 Security Best Practices – Operational and Administrative Tasks" was posted on the Technet website this week. It also covers the nuances of security when using SQL Server SP2 and Vista. Enjoy!
SQL Server service accounts and privileges
Posted on: September 16, 2006 10:29 pm
When installing previous versions of SQL Server, I'd always keep a list of the exact privileges that a SQL Server service account would need. I'd make up a new user and give them exactly these rights. The SQL Server installation program would grant the appropriate permissions during the install, and I'd be set. Principle of […]
Technet Webcast Series For the ITPro – Part 2
Posted on: March 17, 2006 9:18 pm
Today was the first of my two presentations as part of the TechNet Webcast Series for the ITPro, about SQL Server 2005 security. The session went a bit long, as there are so many new security-related features to cover, wouldn't you agree? I got some really great feedback on the key management portions of the […]
What does EXECUTE on an XML SCHEMA COLLECTION allow?
Posted on: February 10, 2006 8:28 am
I've always been slightly puzzled by the permission "EXECUTE" on an XML SCHEMA COLLECTION. Say I have an XML SCHEMA COLLECTION named MySchemas and a table that uses it: CREATE TABLE MyDocuments ( id INT PRIMARY KEY IDENTITY, thexml XML (MySchemas) ) And suppose I have a user named FRED that I grant access […]
Ownership chains and .NET procedure data access code
Posted on: February 9, 2006 7:26 am
Dynamic SQL executed in a stored procedure executes by default using the security context of the CALLER of the procedure rather than the OWNER. That's the way SQL Server has always worked, and although SQL Server 2005 lets you EXECUTE AS OWNER (among other choices), EXECUTE AS CALLER is still the default. So how does […]
Service broker and database master keys
Posted on: November 21, 2005 3:58 pm
Service Broker security is the subject of confusion even among people who think they know how it works. Some of the confusion occurs because security was tightened up in the last few CTPs. I've read in two different places that Service Broker conversations always need to have a master key in the database(s) where the […]
Security in SQL Server 2005 – unsafe assemblies in Sept CTP
Posted on: September 17, 2005 5:13 am
After writing about a lot of new security features that were added since we published our "First Look at SQL Server 2005 book" its nice to report on one that we had in there (at beta2), but never appeared in the product. Until now. In the September CTP version on SQL Server 2005 (I think […]
Security in SQL Server 2005 – “non-traditional” logins and users
Posted on: September 10, 2005 11:40 pm
This one's been around for a while, but don't think we'd mentioned it in the book. In addition to Windows logins and SQL logins, you can have SQL logins mapped to a certificate or to an asymmetric key. Same with database users. One of the reasons you might want to do this is to implement […]
Security in SQL Server 2005 – execute as…with cookie
Posted on: September 10, 2005 11:09 pm
You’ve probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in “CREATE PROCEDURE foo WITH EXECUTE AS OWNER”. But EXECUTE AS can also be used at a session level,like this: EXECUTE AS USER=’fred’ — some T-SQL here REVERT This is meant to replace the SETUSER verb because you […]
Security in SQL Server 2005 – unsetapprole
Posted on: September 5, 2005 7:47 pm
One of the things folks would always ask during the Ascend program was "anything new for application roles? do they support connection pooling yet?". Well, it the most recent CTP (June, July?) there is. You can unset application roles now in addition to setting them. To unset you need to create a cookie, using an […]
Security in SQL Server 2005 – Encryption
Posted on: September 5, 2005 7:32 am
In July 2004, encryption built-ins and key management had just been introduced in SQL Server 2005. Now its old news. They'll be a good-sized section about it in the book revision. You're probably heard of: EncryptBy (Key/Certificate/PassPhrase) and DecryptBy (Key/Certificate/PassPhrase) But there's also the less well-known: SignByAsymKey/SignByCert VerifySignedByAsymKey/VerifySignedByCert HashBytes and also the aptly-named: DecryptByKeyAutoCert and […]
Security in SQL Server 2005 – Logins
Posted on: September 5, 2005 7:08 am
While working on the new edition of our book for after RTM, I've been looking around for security features that I missed or that didn't exist when we wrote it (May 2004 and before). Here's one: In SQL Server 2005, you can DISABLE a LOGIN by using ALTER LOGIN. You can also change the name of […]
Why my cat can’t use a SQLCLR proc to read files
Posted on: August 3, 2005 4:51 pm
I'm known for my vivid imagination when making up test/exposition examples. I have a cat named Sam. So, once upon a time, I wrote: CREATE CREDENTIAL myuser WITH IDENTITY = 'mydomain\myuser', SECRET = 'some56*Z' GO CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3' GO ALTER LOGIN sam WITH CREDENTIAL = myuser GO The DDL works. Now, […]
Changing SQL Login password with the utilities
Posted on: July 6, 2005 11:17 pm
After writing a blog entry on "How DO you change your SQL Login Password" and complaining that "neither" SSMS nor SQLCMD allows you to change this" I was using this feature on the June CTP and, lo and behold, both of these utilities allow changing your password. SQLCMD -? shows -z new password -Z new […]
How DO you change your SQL Login password?
Posted on: June 3, 2005 9:27 pm
SQL Server 2005 will, by default on Windows Server 2003 systems, enforce password policies for SQL Server logins as well as Windows logins. Nice feature, but this means that your SQL Server login password can expire. So how do you change it? Well certainly the DBA can change it as (s)he always has, but you'd […]
How to make a DBA smile
Posted on: May 8, 2005 11:30 pm
Speaking of SQL Profiler brought this to mind. The number one feature that brings a smile to every DBA's face: GRANT ALTER TRACE TO [somedev] No longer do you have to listen to developers ask “make me SA so I can run the trace”. Actually, brings a smile to devs too, no longer do they have […]
Password policies on object passwords
Posted on: January 20, 2005 7:13 am
A new feature of SQL Server 2005 that has been fairly well publicized is the ability, on Windows 2003 operating systems, to enforce password stregth, expiration, and lockout policies on SQL Server logins, as the operating system enforces them on Windows logins. The way that this works is that SQL Server calls NetValidatePasswordPolicy, a Win32 […]
How do I see all the tables again?
Posted on: January 5, 2005 8:08 am
I've been lying pretty low lately, doing research on some topics in a slightly different space (but at least obliquely SQL Server 2005 related). Came across a list of interesting student comments today, and I was back trying out (picking at?) one of my favorite spare-time topics to answer a question about. SQL Server 2005 […]
Schemas, Users, and Objects – III
Posted on: December 3, 2004 6:18 pm
Now, back to our regularly scheduled technical content. About schemas, users, and owners. Although Ed originally created the table, since Fred is the schema owner, Fred owns to table. Ed can get ownership of the table in either of two ways. 1. Someone with authority can alter the table's owner 2. Someone with authority can […]
Schemas, Users, and Objects – II
Posted on: December 2, 2004 7:00 pm
The code for the answer is below. Greg Low is, of course, correct. Fred owns the table, but he owns it by virtue of being the schema owner. Now here's part 2. Do the following: 1. Alter the authorization on Ed's table so that it is owned by Ed. (Interesting aside, can Ed do […]
More on ownership chains
Posted on: November 8, 2004 8:44 pm
People always ask…if ownership chains work the way they do, why do they not work with dynamic SQL? And how about .NET procedures and ownership chains? Dynamic SQL is supported in nearly every database I've run across, but bad dynamic SQL has "issues", to put it mildly. If you create your dynamic SQL via string […]
Users, Schemas, Objects, and Owners
Posted on: November 6, 2004 7:51 am
Separation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED. FRED is the owner of a schema named FRED FRED is a memeber of […]
Least privilege and HTTP endpoints
Posted on: October 20, 2004 5:09 pm
A few weeks ago, I was surprised by an error message when attempting to create an HTTP endpoint with CREATE ENDPOINT. The error was "You do not have permission to perform this operation". The reason I was surprised was that SQL Server 2005 was running in a domain environment and I was logged on as […]
