Voice of the DBA

Tag: security

  • Protecting the SA Account

    The sa account is a well, known, built in account for SQL Server. Years ago, in previous

    versions, I’d see people often use the “sa” account for development, usually with a blank password. Even those speakers and experts often showed demo code with a blank “sa” password.

    Ugh, a horrible example that was repeated over and over until the SQL Server setup program stopped allowing blank password. However people then had “12345” or other simple things to type on stage. I guess some habits never change.

    In any case, I saw someone ask recently about changing the “sa” password in response to auditors’ requests. This person asked about how to randomly set this on a regular basis.

    It’s easy. Even if you have Windows Auth only, you can use this script.

    DECLARE @pwd UNIQUEIDENTIFIER = NEWID()
    , @new VARCHAR(50);

    SELECT @new = CAST(@pwd AS VARCHAR(50))

    EXEC sp_password @new = @new, @loginame = ‘sa’

    That seems to work fine in testing. I get a new sa password each time, whether I have Windows Auth or Mixed mode set. If I change the password with only Windows Auth, the last password set works if I change to mixed mode.

    This is a great little script to set up in a job and run it monthly. This will protect your sa account in case someone ever enables mixed mode.

    If you need the account, just change the password then, for the job/application that needs to run. Then run your job again to reset it.

  • Protecting sa

    Built in accounts are both a help and and a hindrance. Years ago I was working on a SQL Server 4.2 system, where I was an administrator. The database was very unstable, and we were trying to determine if it was something being done by the application or the platform itself. While I was a local administrator, I couldn’t access other, remote SQL 4.2 instances at other locations for our company. Since we ran the same code (supposedly), we wanted to test how various parts of the system performed between the two systems.

    One late night, while actually reading the manual, I discovered the “probe” account, which was a built in account for early Sybase/SQL Server versions. I used this to query remote instance and compare settings and performance. This helped us narrow down the the problems, though it wasn’t the way I would have wanted the system to work.

    Any built in account allows some ease of getting an application working, but it also provides a known backdoor to your system if it is not properly secured. The “sa” account is one of those well known accounts for SQL Server that can cause issues. This account has complete control over SQL Server, and even though it can be renamed, simple queries can discover what the new name is and mount attacks. This is one reason why many people like to only enable Windows Authorization, preventing anyone from logging in with this account?

    However, is this enough? I don’t think so, as a simple administration action could enable mixed mode authentication. I would say that everyone should set a long, random password for “sa” on all instances, but what do most of you think? Do you provide any other protections for the sa account? Let us know today.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and LibSyn. 

  • Open is Not Necessarily More Secure

    Researchers found a bug in glibc. This library has been around for a long time, used in lots, and lots of software. I’m sure there are some programmers unaware that they coded in software that itself used glibc. However, despite all the people that must have looked at this library over the years, no one noticed the bug and disclosed it.

    The maintainers missed the bug, though they took quite some time to patch code once they were aware. That’s disconcerting, especially because many of the routers we use are potentially vulnerable. What’s worse is that many of the consumer based router firmware likely won’t be upgraded as companies would prefer you to purchase a new router rather than actually patch their existing products.

    I wouldn’t be surprised if someone found the bug and didn’t notify anyone publicly. Perhaps they decided to use this bug to attack software with some criminal purpose in mind. What might be scary is that I am sure that criminal organizations, or even various governments, might actively look for issues such as these and take advantage of them.

    I’m glad the issue was found and it can be patched. Certainly having code open for viewing means that researchers and organizations can examine the code, looking for issues. However the fact that software is open doesn’t mean it is more secure. Security depends on careful examination of all code, which may or may not be the case for plenty of FOSS software. I’m not implying FOSS is better or worse than closed source, vendor written software. Just be aware that you can’t assume it’s more secure.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Create a Database Master Key–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    One of the first things you need in a SQL Server database in order to implement encryption is a database master key, DMK. This is simple to create, though you need one in each database that will support encryption.

    The syntax is easy, with only really an option to specify a password. There is no name, as there’s a single DMK per database. Set your context to the correct database and end enter:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Som3thingR3ally$|tr0ng’;

    When you execute this, you’ll just get a result message. At least, if it works you will. The password must conform to the password requirements of your Windows OS, which is good.

    Note: This is a securable code, like the password for a user account. Make sure you store this in a password manager for your organization.

    By default, this is protected by your password as well as the Service Master Key (SMK) on your instance. In practice this doesn’t usually mean much for you, but be aware of this.

    You do need CONTROL permission on the database, though usually I’d expect a db_owner or more permissions to actually create these keys.

    And, of course, back up the key as soon as you can.

    SQLNewBlogger

    This was about 5 minutes work for me. I would guess most new bloggers could read, understand, and produce an explanation of this in 30 minutes.

    References

    CREATE MASTER KEY – https://msdn.microsoft.com/en-us/library/ms174382.aspx

    Create a Database Master Key – https://msdn.microsoft.com/en-us/library/aa337551.aspx