Configuring SQL Server Express 2005 - Next – Connecting to Your Application

(Page 4 of 5 )

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_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

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!

Next: The Connection String >>
 

More MS SQL Server Articles
More By Justin Cook

Comments On: Configuring SQL Server Express 2005

· I just wanted to leave you my compliments and thanks for the great article. I had...    ·     · ----------------------------------------Get free email at...    · At the second step when I enter the command for EXEC sp_grantlogin...    · Many thanks - I found your article very helpful. Just one thing. I believe that the...    · Same problem I am having.    · Can you register SQL 2005 Express server with SQL Enterprise Manager (GUI tool to...    · What is going to happen when the app is published to a website on a server operated...    · I came across the same error when I tried using MachineName/UserNameWhat I tried...    · Thanks for the amazing tutorial. Got me up and running with no errors in 5 minutes!...    · tnx, it has been very helpfull    · Hi this article looks very relevant however before i start i would be grateful...   >>> Post your comment now!