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
/ 45 
