To connect with contained user credentials you have to specify contained database in the connection string. If no database is specified the connection will try to do traditional authentication as a login in master database. If the database does not support containment, then the user will be logged into master and then connect to the… Read more
With the release of Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1) and the introduction of Contained Database (CDB) (http://msdn.microsoft.com/en-us/library/ff929071(SQL.110).aspx ), we also introduced the capability of database authentication (http://msdn.microsoft.com/en-us/library/ms173463(v=SQL.110).aspx , http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/03/contained-database-authentication-introduction.aspx, http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/04/contained-database-authentication-monitoring-and-controlling-contained-users.aspx). Since the configuration setting that governs CDB & database authentication is a server scoped setting and the option to… Read more
Enabling contained database authentication on an instance allows db owners (and other privileged db users) to create and manage users who can connect to the database on the instance. However, the instance administrator (or other privileged server principal) may want to monitor database authentication – users and connections. Here are some queries which should help… Read more
In Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1) we introduced the Contained Database (CDB) feature. As the name suggests, self-contained database have no external dependencies. Contained databases can therefore be easily moved to another server and start working instantly without the need of any additional configuration. One of the key features of… Read more
Andreas Wolter recently posted yet another reason to keep guest disabled on user databases in SQL Server. He also points out some reasons why developers shouldn’t have access to production systems, but I’d like to focus on the implications for guest. As Andreas summarizes at the end of his post, “never use the guest account… Read more
Many applications need to generate random data, and in order to help in this task they typically rely on pseudorandom number generators (PRNG). Typical PRNGs are deterministic in nature and therefore they are not cryptographically suitable, this is the case of the built-in RAND (http://msdn.microsoft.com/en-us/library/ms177610.aspx) in SQL Server. If your T-SQL application… Read more
Rick Byham, our wonderful technical writer, just posted some checklists you may find useful on the TechNet Wiki. You can search the wiki for word checklist or use these links: Database Engine Security Checklist: Encrypting Sensitive DataDatabase Engine Security Checklist: Enhancing the Security of Database Engine ConnectionsDatabase Engine Security Checklist: Limiting Access to DataDatabase Engine Security… Read more
In my previous post I talked about DEK management and how it is stored in the database. In this post I will try to give an overview of how the database log file is encrypted by TDE and what are the implicataions of key rotations (DEK or encryptor changes) on the log file. TDE encrypts… Read more
This post will talk about DEK, what it is and how it is securely stored and managed inside a database. Before enabling TDE a DEK must be created which is used to encrypt the contents of the database. It is a symmetric key and supported algorithms are AES with 128-bit, 192bit, or 256bit keys or… Read more
Transparent Database Encryption (TDE) was introduced in SQL Server 2008 to allow users to encrypt databases without affecting any applications. Before reading this blog I would suggest reading Sung Hsueh’s whitepaper on TDE and MSDN as it covers a lot of basics. In this blog, or rather series of blog posts I will discuss some… Read more