You can win a free conference pass to SQL Bits, in Liverpool, May 4-7. It’s easy, just post a review of one of their previous events. Haven’t been? No worries, post a review of a video of one of the sessions. You can find those here:
If you want to just register, there’s a few discount code slots left, so use 4pr1l-F00l when you register.
This is my favorite event, and I hope to see you there.
I had never seen this, but I ran across a blog that mentioned an MDF File password here. The post really looks at ways to reset the administrator password for the “sa” account in SQL Server. However it has some mistakes and issues. I tried leaving a comment, but comments are disabled.
With that in mind, I decided to respond to a few things and clear up confusions.
With regards to the post, I think it’s confusing in that the text notes an MDF file password, but all the instructions are really about resetting the “sa” account password. sa is the built in sysadmin account in SQL Server, which isn’t related to the MDF file. The MDF file is the extension of the main data file for a database. You can change this, but there isn’t a good reason to do so. Note, the .ndf files are the same format, though by convention, these are the 2nd, 3rd, and other files added to a database.
There also isn’t a password on these files. I can open them in notepad (not recommended) or xvi32, and there isn’t any requirement if I have read access in NTFS to the file. It doesn’t matter if this is the master database or any user database. If you have NTFS permissions, you can read the file.
Now interpreting is different. SQL Server interprets this, and it requires permissions itself to access the server process, either sysadmin, or normal login. However, you can use ORCAMDF or MDF Viewer, or some other tool to read the files. The information contained in an mdf/ndf file is just formatted in a certain way. If you spend a lot of time, you will understand how to interpret the format.
Changing the sa password requires that the SQL Server service be running and you connect in some way. The post gets the methods right, but says that you must stop the service, which is only needed if you access the file some other way (ORCAMDF, xvi32, etc.). If you want to change the sa password, there are a few choices:
Any of these first four will work, and feel free to use whichever fits your situation. The last one is one I do not recommend as I can’t be sure any third party products will work correctly here.
Ultimately I’m a little embarrassed by this post, as it appeared through our syndication process on SQLServerCentral. We don’t review these posts, so there is no quality control. Most of the posts on this blog are good ones, but this one appears to be by a guest author and it’s one I’d ignore.
What do you need to do in order to access data in a SQL Server that’s encrypted with Always Encrypted? It’s not much, and it’s really simple.
That’s it. It’s a small list of things.
I was experimenting with this, and I set up encryption on a VM, then copied the certificate backup to another VM and installed it in the Certificate Store.
This is all I changed on my C# application to enable encryption.
strConnstring += “; Column Encryption Setting = Enabled”
I had a connection string built, and I added this one little option to the end and when I queried my encrypted table, I could read the data.
There are certainly more caveats and more to learn about encryption, but this shows how easy it can be to change your application. Just alter the connection string.
When I started working with SQL Server, the sa account was “the” account used for admin operations. This was the default account for many DBAs and as a result, it couldn’t be locked out.
This changed in SQL Server 2005, which is a good thing. We don’t want unlimited attacks on the sa account with brute force password guesses. I wasn’t aware of this, as I haven’t had an issue with attacks in a long time. However Jeff Moden pointed out to me recently that we can lock out sa.
I decided to test.
First, I went to the local policy on my desktop and checked the security policy. No lockout was set, which probably makes sense for consumer OSes. I took a minute to then set my lockout to 5 attempts with a 30 minute timeout.
I then restarted my SQL instance. I couldn’t get this to lock me out at first, so I decided to ensure the policy applied.
I then tried logging in with the sa account 6 times with the the wrong password. Each time I got this message. Including the 7th time with the correct password.
No note about being locked out. However when I check the properties for sa, I find the login is locked out.
I could uncheck the box, but I can easily use T-SQL as well.
ALTER LOGIN sa WITH PASSWORD=’test’ UNLOCK
Please don’t use a password like this. I actually ran this to test and then reset the password to something more complex.
ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx
Voice of the DBA 