Voice of the DBA

Category: Blog

  • #SQLNewBlogger–Adding Local Accounts

     

    What do you do if you need a process running under Local Service to connect to your SQL Server? Most of the advice out there is to change the login account. I actually agree with that, but there are times you can’t, or don’t want to.

    There are certainly times when I’ve seen some automated process use one of these accounts:

    • NT Authority\Network Service
    • NT Authority\Local Server

    Often this is because someone doesn’t want to bother to learn how to enable other accounts for their application, which isn’t a good excuse. In my case, I had a local VSTS agent service running as part of a demo, where I had very limited rights. I couldn’t affect a change, and I needed to get a new login for SQL Server.

    I searched a bit, but most advice said to just change the account, after all, if you had a process connecting from another machine, Local Service won’t work. However I found one item on Stack Overflow that helped.

    Here’s my Login list. As you can see, I have Network Service, but not Local Service.

    2016-03-25 12_50_57-Alarms & Clock

    I the run this code:

    CREATE LOGIN [NT AUTHORITY\LOCAL SERVICE] FROM WINDOWS;

    This gives me a new login.

    2016-03-25 12_52_48-Alarms & Clock

    In my situation, I then had to add this to the dbcreator role, but I could treat this like any other login and assign the minimum privileges needed.

    SQLNewBlogger

    I had to solve this and decided to write about it. The writing took 10 minutes, the research was 15-20 minutes to find a good reference and experiment a bit.

    A good learning exercise, and all of you should know how to do this. Prove it with your own blog.

  • 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.

  • SQL Nexus in Copenhagen

    The Nordic SQLNexus conference is taking place in Copenhagen on May 2-4. I’ve never been to the event, or the country, but I was accepted to speak, so I embark on another multi-city, multi day trip.

    SQLNexus has quite a lineup, and I suspect a few of these people will be travling alongside me, in Copenhagen at the beginning of the week and Liverpool at the end.

    • Joseph Sirosh, Microsoft Vice President
    • Troils Peterson, Professor of Particle Physics at the Niels Bohr Institute
    • Allan Hirt
    • Itzik Ben Gan
    • Denny Cherry
    • and more

    I’m looking forward to the event, and if you want to come to a SQL Server conference packed with content, think about making your way to Denmark on May 2-4, 2016.

    Hopefully I’ll see some of you there.

  • Default Data Masking

    Dynamic Data Masking is a neat new feature in SQL Server 2016. I didn’t think much of it when it was introduced in Azure SQL Database, but since then I realize there is some value here. Even if it’s just making life simpler for developers.

    I’ve been experimenting with this a bit, learning how it works, and one of the options we have for masking data is to use the default option. However, what seems misleading to me here is that this doesn’t use a default from the column. Instead it replaces the values with

    • 4 x’s (xxxx) if the column size is > 4 characters (same for numerals)
    • the number of x’s that fit in the column if the size is < 4.
    • 0 for numbers

    This makes some sense, but not completely. I think I’d prefer to set a default mask for all types, so that I don’t disclose a value is a number or string (or date or anything).  I also see that NULLs are disclosed, another potential area I’d prefer to keep hidden.

    I also think the name is misleading. I’ d prefer to see this called something like xmask, or defaultmask, not default.

    If you want to learn more, you can look at a piece I’ve written to cover how this works, details on the default mask, or check out our list of resources at SQLServerCentral.