Configuring SQL Server Express 2005

The new Visual Studio Express tools are truly sweet, even though still in Beta. This holds true for SQL Server Express Edition, the free new powerful database system. The only problem with the Beta product is that there’s no real graphical configuration tools, so getting the thing to actually run can prove to be a hefty task. This article will provide all the necessary instruction to get up and running happily.

I remember it like it was yesterday. It was however July 2nd, and it was a Friday. I had just received news of the Visual Studio Express tools hitting the world, and this was a very exciting thing. I was particularly excited to see that Microsoft was now offering a free, scaled-down version of SQL Server 2005, called SQL Server Express.

I turned on my home computer and downloaded the package of Web Dev Express, ASP.Net 2.0 Framework, and SQL Server Express. I installed all three. My excitement still ran strong while I fired up Web Dev Express for the first time.

Every trace of my excitement was smashed and crushed as I clicked on Database Explorer, tried to connect to SQL, and received my first (of many) error message. Within a relatively short period of time, there was no excitement left, simply frustration, but thankfully a good amount of determination.

The following is the result of my entire weekend spent agonizing, searching, yelling, troubleshooting, screaming, and finally exulting in my efforts to configure SQL Server Express 2005.

{mospagebreak title=The Easy Part – Installation}

The easiest way to download and install SQL Server Express is with the installer for any of the other Express tools, found at http://msdn.microsoft.com/express. You can download it on its own and install, but the major problem with this route is that there is no GUI tools, you need the ‘database explorer’ in the other tool(s) to actually administer the database. Not to mention that the very reason you’re going to be using the system is to create dynamic applications and websites, so you’ll need the other tools anyways!

I used Web Dev Express for working with Asp.Net. A word of caution: I tried to use the installer to do the downloading from behind a proxy, and it wouldn’t work. At home I was able to use the installer, at work I was forced to download the individual packages for Web Dev and SQL Server Express, as well as one for the .Net 2.0 framework.

Anyways, once you’ve downloaded the packages, it’s mind-numbingly easy to do the installation. Just allow the install shield to install with all defaults.

But that’s where the ease ends. You see, the installation of SQL Express is nothing like that of SQL Server 2000. Here are some potential headaches:

  • You are at no time prompted for the desired server name. This is set by the installer, and you are given absolutely no indication as to what it is.

  • In an automated install you don’t get to set the ‘sa’ password, it’s set to a random strong password by the installer.

  • I feel the need to mention this again, as it’s really the driver behind this article: there are absolutely no GUI admin tools provided with SQL Express!

So without knowledge of how to connect to the server, what authentication to use, and what to do once you’re connected, you’ll soon be seeking some Aspirin! To prevent hours of frustration and hundreds of “access denied/authentication failed” messages, read on!

{mospagebreak title=The Not-So-Easy Part – Configuration}

No doubt you strive for maximum security (the maximum possible within Windows that is) on your home PC. One of the most basic rules to follow in that case is not do your regular computing tasks as Administrator. Have an administrator account for installing applications, but then run as a regular ‘user’ for all other tasks, including programming.

The unfortunate result of taking that precaution, is that as a user you won’t be able to login to SQL Express, only the administrator will. So we need to change that. But how do we do such a thing without a GUI admin tool? Simple, we go back to the old command prompt!

To do this I need to clarify two things.

  1. The SQL server name until this point has remained a mystery. Microsoft has decided that we have no need of changing the name, so it is preset as [MachineName]SQLExpress. You can use ‘localhost’, ‘(local)’, or even a period in place of your machine name, but I recommend specifying your machine name at lease in you application configuration files, so that you can easily spot where and what to change if you move SQL to another box, unlikely though that is.

  2. Though there is no GUI tools, Microsoft has not left us entirely up the creek. They provided a command line tool, called ‘SQLCMD’.

So, fire up your command prompt (as administrator) and type in:

SQLCMD.exe -S (local)SQLExpress -E

So now we are interacting with SQL Server Express, which as mentioned runs as ‘SQLExpress’ on the local machine.

To actually add a user, we’re going to use the SQL Server System Security Stored Procedures. For now, these are the only options we have for administering security. The one we want right now is ‘sp_grantlogin’. This will allow us or others to login to SQL server from non-administrator accounts. Type in:

> EXEC sp_grantlogin ‘[machineName][userName]’
> GO

Obviously you’re substituting your machine and user name in there. Just thought I’d clarify that. Now we can add the user to SQL with another stored procedure: ‘sp_adduser’. Type in:

> EXEC sp_adduser ‘[machineName][userName]’, ‘[userNameInDB]’
> GO

It’s good to specify the second parameter, the ‘userNameInDB’, so that you don’t have to type in their full machine and user name every time. For instance, you could use ‘Joe’, and reference that user by ‘Joe’ in the other stored procedures that will come up.

You now want to make yourself a sysadmin, so that you can login to SQL Server from your user account and still have full abilities within the system. For that we will use the stored procedure ‘sp_addsrvrolemember’. Type in:

> EXEC sp_ addsrvrolemember ‘[userName]’, ‘sysadmin’
> GO

And there you have it, you will now be able to log in and administer databases from the ‘Database Explorer’ within the express tools. Use these tools to create a new database. Remember the name of the database, as you’ll need it for the next part! You can do this very easily by right-clicking on Data Connections, then click ‘Create new SQL Server database…’, and follow the wizard.

{mospagebreak title=Next – Connecting to Your Application}

Now of course we haven’t configured enough to actually hook the database server to your web application. We could only accomplish that if you’re using integrated Windows authentication on your web application, and you’re the only one using it. And of course as a web developer, you know the vast improbability of and problems with those two assumptions.

So basically, we need to create a login account for the web application. For this we’ll use a SQL Server login, not Windows. So we will need to user different stored procedures. What are they?

Well before we even get into that, there’s a slight problem. When SQL express installs, it installs in Windows Authentication mode, which is exactly what we don’t want. We want Mixed Authentication mode, which will allow us to login from Windows, or with a SQL login. And once again there’s no GUI tool to enable that.

So fire up your good old ‘regedit’, it’s time to hack about in the registry itself! Navigate to:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServer

You’ll see that the key ‘LoginMode’ is set to 1. Just open it, and change it to 2! That’s it! Easy as pie! (what’s the point of that expression, is pie really that easy?)

Now we’re able to add a SQL Login. We’ll use the stored procedure ‘sp_addlogin’. Make sure you have the SQLCMD open, and type in:

> EXEC sp_addlogin ‘[newUserName]’, ‘[newPassword]’
> GO

By the way, be sure to use a strong password, for security purposes. This will be encrypted and stored in the system table. Your safest bet is to have a different login for each web application. Now all we have to do is give the user we create permissions on the database we created. You should never use the ‘sa’ account from within your applications! For our task, we’ll use one last stored procedure: ‘sp_grantdbaccess’.

But first we must actually use the database we created. You can do that one of two ways. If SQLCMD is still open, type in:

> USE [database name]
> GO 

or, re-open SQLCMD with -U parameter, like this:

SQLCMD.exe -S (local)SQLExpress -U [database name]

Now that we’re using the new database, we can grant the user permissions. Type the following:

> EXEC sp_grantdbaccess ‘[login]’
> GO 

And there you have it, you can log in, and so can your web application!

{mospagebreak title=The Connection String}

You may come from an ASP background, where you’re used to stuffing the connection string into an application level variable, and retrieving it each time you need to access the database. Well you can do that with .Net as well. You can use the web.config file, and write application level variables in an XML format.

In ASP.Net 1, the easiest way was to stuff the string into the appSettings section of the web.config. Now with ASP.Net 2.0, Microsoft has realized that one of if not the most commong thing going in there is a connection string, so they went ahead and created a custom section. This is conspicuously called ‘connectionStrings’, and you can add a key as you would any other section. A connection string key takes three parameters: connectionString, name, and providerName.

You can use this new feature, or the plain old appSettings. Whichever you choose, the connection string will look like this:

Server=(local)SQLEXPRESS;
User ID=[userName];
password=[password];
Database=[databaseName];
Persist Security Info=True 

Conclusion

The new SQL Server 2005 Express is truly a great tool for developers. I think Microsoft has done it something of an injustice by hailing it as a tool for ‘hobbyists and amateurs’. It’s fast, lightweight, and very tightly integrated with the .Net 2.0 framework. Once Microsoft releases a GUI administration tool for it, life will be far simpler. But until that time, I hope you are spared many hours of frustration by using the methods described in this article!

By the way, if you would like more information on the SQL Server System Stored Procedures, visit the documentation in MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

12 thoughts on “Configuring SQL Server Express 2005

  1. I just wanted to leave you my compliments and thanks for the great article. I had tried and failed everything else and you gave me a cake recepy to follow.

    Thank you very much!!

  2. At the second step when I enter the command for EXEC sp_grantlogin ‘[machine]\[username] > GO, I get an error Windows NT user or group ‘[machine]\[username] not found. Check the name again.

    The machine name I’m using is the computer name and the username is the default username…what could be wrong?

  3. Many thanks – I found your article very helpful. Just one thing. I believe that the -d switch (not -U) is required in the lines below on page 4:

    re-open SQLCMD with -U parameter, like this:

    SQLCMD.exe -S (local)\SQLExpress -U [database name]

  4. Can you register SQL 2005 Express server with SQL Enterprise Manager (GUI tool to manage sql server) and administer Databases from this GUI interface?

  5. What is going to happen when the app is published to a website on a server operated by a hosting service that also admins the SQL server farm? It will crash. Shouldn’t the documentation be real world? Notice they never provide a roadmap on how to build and deploy a tightly coded, secure application to a hosted server, which is probably what 50% of the users are attempting to do. There seems to be a vicious circle where by the time someone finishes reading a couple of those 3″ thick books on the software, it goes off and becomes obsolete.

  6. I came across the same error when I tried using MachineName/UserName

    What I tried is DOMAIN NAME/User name.
    That worked!
    Anonymous.

  7. Hi

    this article looks very relevant however before i start i would be grateful if someone would kindly confirm that i need to carry out the steps mentioned…

    I have Windows Sever 2003 installed and i have recently downloaded onto the same machine a free version of Sql 2005 Express. When i try to connect to the server (File < Connect Object Explorer)i get the following message:

    Cannot connect to home.local. An error has occurred while establishing a connection to the server etc

  8. It would save readers much time if redundant statements like “That’s it! Easy as pie! (what’s the point of that expression, is pie really that easy?)” is removed.

    If the article can be summarized, minutes of everyone can be saved.

  9. Excellent and well written thread which has saved me no end of time and hassle.

    Does exactly what it says on the tin – Well done

[gp-comments width="770" linklove="off" ]