You may be aware that I am still keen on SQL Server and despite the changing nature of my role at Microsoft I still try and keep my hand in with the latest version. One thing I overlooked until I did SQL Relay was contained database security which is easy to use and can make your life a lot easier.
In previous versions of SQL Server logins are stored in the master database whether you use windows or SQL Server authentication. That’s fine until you want your database to move to another instance of SQL Server. For example if you enable mirroring your database could end up running on a secondary server and if you haven’t got a separate process to move the logins across to the secondary this could cause problems if those logins have changed or new ones have been created.
SQL Server 2012 fixes this with contained databases and makes deployment easier by enabling you to put the logins into the application database. As well as making database more portable it also means that uses connect to specific database rather than to the whole the instance thus limiting what they can do to just that database. For example they won’t even be aware that there are other databases in that instance.
For this to work you need to alter the properties of the instance either in options form the GUI:
or using the following TSQL:
EXEC sys.sp_configure ‘Contained database authentication’, 1 GO RECONFIGURE WITH OVERRIDE GO
EXEC sys.sp_configure ‘Contained database authentication’, 1
RECONFIGURE WITH OVERRIDE
I would then configure my database for contained security again either form the UI..
or in TSQL..
USE Master ALTER DATABASE [DeepFat] SET ‘containment = PARTIAL WITH NO_WAIT GO
ALTER DATABASE [DeepFat] SET ‘containment = PARTIAL WITH NO_WAIT
If I then create a login..
USE [DeepFat] CREATE USER [Deepfat] WITH PASSWORD = ********
CREATE USER [Deepfat] WITH PASSWORD = ********
I can do a simple test to see how this works. I open Management Studio and try and use this login to connect to my instance and it will fail..
because I also need to specify the database I am going to (by expanding the options and selecting the database to connect to) …
If I do that I will be able to connect but all I will see in management studio is my database..
This will work for all the other kinds of logins like windows authentication. So a nice simple way to contain the enthusiasm of your uses to wander around your SQL servers as well as making the database more portable.
If you want to try this you’ll need to use SQL Server 2012, and you might want to see what else is new in this release on the SQL Server track in the Microsoft Virtual Academy
I wanted to something different for SQL Relay last week as this is a community driven event and I didn’t want to cover areas that the other experts on the Relay team would cover. This left some gaps and I have had several follow up questions via twitter and e-mail which I want to cover in this post.
I covered several uses of the in memory column based technology in my session 2 of which are in SQL Server 2008 R2 (PowerPivcot for Excel and PowerPivot for SharePoint) and two are new for SQL Server 2012: Tabular Analysis Services and Columnstore indexes. Columnstore indexes are part of the database engine rather than part of Analysis services and are created much in the same way as for other indexes:
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory] ON dbo.FactProductInventory ( ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance )
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_CS_FactProductInventory]
ProductKey, DateKey, UnitCost, UnitsIn, UnitsOut, UnitsBalance
They can speed up a query by 10-100x compared to a normal index however you can’t update a table with a Columsntore index on you have to disable it and then re-index after you have made your changes. There a good guide on its usage on the SQL Server wiki.
I would like to have covered of contained database security which as the name suggests means that the login credentials of users get stored in a given database rather than in master and for SQL Server authentication this means that the password is in there as well. As well as making the database more portable a user who just has their credentials in that database can’t change to another database and have little or no permissions outside that database. Therefore when you connect to a contained database you need to specify the database as well as the server/instance (for example in management studio) I can see there being huge advantages of this approach for developers wishing to make applications cloud ready and ISV’s can make deployments of their applications more easily.
I also got asked about crypto enhancements in SQL Server and these are:
I have to admit to glossing over this as I am not primarily a developer and I rarely play with SQL Server Express. LocalDB is an installation option in SQL Express and allows for a cutdown version of SQL Server to run against a local database. Note LocalDB doesn’t support Filestream and cannot be a merge replication subscriber and for more on this refer to LocalDB in MSDN
I have now checked and there is currently no drill down capability in Power View. If htis is important to you r business you can register your interest in this feature oadn/or clusteringn Connect (Microsoft portal for feedback on products)
SQL Server Guest Clustering/Always On
No matter how good your virtualisation stack is if you want to make SQL Server highly available you need some form of solution whereby a virtual machine can hand off the running of a SQL server database instance to another virtual machine for planned and unplanned downtime. Correct me if I am wrong but Vmware DRS simply doesn’t do this (neither does Hyper-V so I am not trying to criticise Vmware per se). Your choices were mirroring before the launch of SQL Server 2012 and now that’s out you have Always On. This should work well on Vmware as well as Hyper-V as there is no dependency on shared storage and hence iscsi support in your VMs.
So hopefully that helps clear up a few things I had to skate over in the interest of time, do ping me if I have missed your query off and look forward to chatting with you all agin at SQL Bits or some other community event soon.
Curiously one of the topics we hardly ever get asked about at our IT Camps is security in Hyper-V. Perhaps it’s because you all have total confidence in our approach security, or you already have the facts to hand, but more likely is that you forgot to ask about because it’s not top of your agenda.
That’s OK, and anyway I need to write this so I have the definitive answers to hand when someone asks me.
So what do you need to consider when virtualising your data centre?
The best resource I have seen is by the US Government specifically the National Institute of Standards & Technology in their Guide to Security for Virtualization Technologies. It’s a big read but the three key sections are:
4-2 recommendations about locking down the hypervisor. the key points are:
4-3 recommendations for securing the virtual machines themselves
4-4 recommendations for securing a virtual desktop infrastructure
I would argue that you’ll also need System Center to manage your data centre security, check and rectify compliance issues as well as to audit and changes. To help with that there is a Governance Risk & Compliance Process Pack which uses the integration between Service Manager and the rest of System Center (Config Manager, Ops Manager, Virtual machine Manger via Orchestrator). It has extensive guidance for the non IT functions and has the side benefit of showing you how to unify System Center to better support the business.
Finally You’ll want to lock down windows server as well whether that’s the physical operating system or the guest and there’s a Security Compliance Manager to help with that.
Four cities, in four days makes for one tired evangelist, so why am I doing SQL Relay with the SQL community I hear you ask. Well that’s your answer really - I can’t hear you ask anything when I am sat in darkest Surrey blogging away so I am actually doing these events not so much to speak as to listen. For a start we have some of the best experts on SQL Server in the world giving up their time to go on tour as well so I can learn from them. More importantly I am interested in what’s going on with SQL Server in the real world; are you virtualised, are you still on SQL Server 2005 or even 2000, and are you dedicated or is SQL just something you do in between Exchange, and Active Directory.
With lots of Power% and %Point stuff in SQL Server I thought I might skip the actual PowerPoint to avoid confusion. I also want my talk to be based on what you want to hear about so if you do plan to go:
and the following we’ll be in London on 30 May
Please register, and then drop me a tweet (@deepfat) or a drop me an e-mail with your questions comments or suggestions, and I’ll see if I can get some swag for those who send me ideas