Voice of the DBA

Author: way0utwest

  • The Best of the Best

    Today’s editorial was originally pubished on June 26, 2007. It is being re-run today as Steve is traveling. 

    Men in Black
    The best of the best of the best, sir.

     

    Of the best of the best, sir! (remember that one?)

    Where’s the best place to work? Here’s the list from Computerworldfor 2007. I’m not surprised that a few of these companies (University of Miami, Quicken Loans) were in last year’s list. After all, if you’re a great company, you should be able to maintain that in the short term.

    What’s more interesting, is that in the various lists (Best overall, Diversity, Benefits, etc.) is that a number of companies show up multiple times. For example, the University of Miami shows up in 4 of the 5 top tens. With the weather, maybe that’s the place to be.

    I’m not sure I’d use this list as a way to seek out a job, but I’d certainly check it if I were applying somewhere because it shows that there are people that like the company. That’s important since I’m of the opinion that the people you work with and their attitudes are extremely important to having a good term of employment.

    But most important is that you need to find the best fit for you. I interviewed with Pulte Homes (#69) a few years ago (2002?) and wasn’t impressed with the people. In fact, I wouldn’t have wanted to work there. Maybe things have changed since then, but I know I’d be wary of going back there as the culture didn’t seem to fit me.

  • Creating a User without a Login – Contained Databases

    In SQL Server 2012, we have a new feature: partially contained databases. In a previous post, I showed how to enable this, and this post will look at one of the advantages of contained databases: users without logins.

    Contained Users

    One of the problems in non-contained databases is the fact that when a database is moved or restored, the login mapping to the user in the database doesn’t always transfer cleanly. Microsoft has sp_help_revlogin and sp_change_users_login to help fix this, but in a DR situation, or in a crisis, this may not work. It’s also a hassle.

    Contained users help fix this. They are users that exist within the database, and do not require a login mapping. The server level authentication will transfer to a database level authentication, if the database has the partial containment option set.

    To create a contained user, you can use the GUI, or T-SQL, both of which are easy and I’ll show them below:

    SSMS Contained User

    If you right click the Users folder (under Security) in a database, you can select the New User option.

    cdb3

    When this appears, you can then use the drop down to select a User with a Password option for a SQL Server user that is contained inside a database.

    cdb4

    The traditional user is a user with a login. Here’s the dialog from SSMS 2008, with no option for a user without a password.

    cdb8

    Back to 2012, I can enter a user name and password, and then I have a user in my database.

    cdb5

    The process for a Windows user (again, without a login) is similar. I can select a “Windows User” and then select the ellipsis by the User name and search for an AD user.

    cdb6

    This looks the same when I accept a user

    cdb7

    I can set a default schema here, and a language, but I don’t need the login.

    T-SQL

    The process with T-SQL is the same. The code for the CREATE USER command is simple:

    create user Billy with password = 'Billy2Goat$Gruff'
;

    If I want a Windows user, I can do this:

    CREATE USER [DKRSQL2012\Andy]
GO

    Note that this is domain\user syntax. Some AD tools allow the user@domain syntax, but this isn’t allowed in SQL Server 2012 for the CREATE USER command.

    You can replace the user name with a group at the database level, and the syntax is the same.

    Summary

    That’s it. It’s simple, and in another post, I’ll look at authentication.

  • Key Storage

    keys
    Hopefully your digital key storage is more organized and secure than this.

    One of the issues with encryption, perhaps the biggest issue, is the management of the keys that protect the encrypted data. I have been an advocate of keeping the backup of the keys far away from the backup of the encrypted data.I usually want them on separate media, or a separate tape, just so that a loss of my backup of the data (or the data itself), doesn’t include the key.

    However this presents a problem in a DR situation, especially over time. If I make a backup, and lose my server in a year, can I easily find the copies of the asymmetric keys or certificates? Can I easily match up the proper key with the encryption if I rotate keys periodically? There hasn’t been a great solution I’ve seen to solving this issue.

    Recently I saw a talk on security, and the speaker mentioned they kept copies of their certificates on the backup tape with the backup of the data. This person felt that since a password was needed for the certificate, that this was secure enough. Perhaps, but you still have the problem of securing that password over time as well. This week, I wanted to ask those of you that use encryption, how do you handle the issue.

    Would you store a secure asymmetric key protected with a password on your backup drive or tape?

    If so, then how do you handle the security of the password? If not, then what other solution do you have? I know key management is a struggle in many organizations, but if you have something that works for you, let us know how it works.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Transfer Times in the Cloud

    SQL Azure
    Can you get your data out of the cloud if it goes in?

    There are many reasons not to move your data to the cloud. You might be concerned about the performance of your system, you might be concerned about the reliability of the service, you might worry about any number of regulatory or legal factors, or you might be worried about security. However I think most of those issues aren’t as important as another one: moving your data.

    I like the freedom to choose how I want to run my business. I like to be able to choose to run Oracle, or NoSQL, or SQL Server. I like to be able to purchase my hardware from Dell or HP or Apple. And more importantly, I like to be able to change my mind and move to a new vendor if they have a better product, or if the price saves me money, or if I just change my mind.

    The ability to get my data back is critical to me. Despite all the triple redundancy of Azure and the backups avaible from AWS, I want to be able to move my data if the need arises. There are transfer options, but they might not work as well as expected. A test moving data from AWS to Azure didn’t work as well as expected, which unfortunately, isn’t surprising. Part of this may be Azure and not Amazon, which is a little disappointing.

    I suspect that cloud companies would not be as helpful when you are closing your account as when you first sign up. They have no financial incentive to do so, but I would hope that they would act maturely, and let customers go if they cannot meet their needs. Our requirements may change,  and being able to get our data back is important. If you want to charge a fee to expedite the removal of data, state that up front, but make sure that we can get our data back.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.