Back in 2007, I began working on a web project using Visual Studio 2008 and SQL Server 2005 Express as the back-end database. OK, thought I, I’ll start with the authentication controls. Asp.Net framework v2 handles it very well, taking all the donkey work out of all the coding for login forms, registration forms, forgotten passwords etc etc.
So, I found an excellent online video demo of how to build it. First things first, create a database. So I did. Didn’t create any tables yet, but I didn’t need to, not yet anyway because there’s this clever little utility called aspnet_regsql which allows you to set up the login tables and so on.
The wizard opens and you enter the server and choose a database from the list (which it populates from the SQL Server). Well, it should do, but chose not to, spitting out the error “Named Pipes Provider Error: 40 – Could not open a connection to SQL Server”.
After a lot of digging and searching for error codes, it appears that it can’t be done with SQL Server Express. So, I’ve uninstalled it and am about to install a trial of the grownup version.
****
So, I now have the 180-day evaluation installed, and am trying to connect to the server using the aforementioned aspnet_regsql utility. I kept getting the same old Named Pipes error (40). So, I again added a password to my windows user, and used that to log into the server. Still getting the same error.
Incidentally, every time I make a change, I have to restart the SQL Server service, which takes about 15 seconds on average.
I make sure that Named Pipes are enabled. Same error. I make sure TCP/IP connections are enabled. Same error. I open the port for the named instance on Windows Firewall. Same error. I use another little utility called cliconfig which also allows me to enable TCP/IP and Named Pipes connections. Getting the same error.
This is getting annoying. I’ve read through about a dozen blogs and forums on the MSDN site, all to no avail. I then enter the server name as server/sqlinstance. Nothing. As a last ditch attempt, I use a backslash instead, thus entering server\sqlinstance.
JOY! A different error. That might sound silly, but to me, that’s progress.
I load up SQL Server Management Studio and log in with Windows Authentication. While I’m in there, I attach the database I want to use. No error messages, it’s looking good. I then create a new login, select my database as the default and click OK.
An exception occurred while executing a Transact-SQL statement or batch.
It says I used invalid characters! (Error 15006). Did I hell. Hang on, maybe I did. It entered the login name as server\Mark. I hastily change it to Mark and click OK.
The MUST_CHANGE option is not supported by this version of Microsoft Windows (Error 15195).
For the love of all things holy. No wonder database experts get paid so much, it’s so they can pay for therapy when they have a breakdown every couple of years. I enter this new error into Google, find another MSDN blog and pull some more hair out before it turns white hopefully.
A-ha! It appears that SQL 2k5 has lots of security features such as password expiration and so on, but these only work with Windows 2k3 Server. So what the bloody hell do I do on Windows XP?? Then I see it. A check-box labelled User must change password at next login.
I unchecked the box, click OK and it accepts it!! HUZZAH! I celebrate by dancing a dainty jig into the kitchen and put the kettle on.
My breath suitable bated, I run aspnet_regsql and log in. ARGHH! It won’t let me login. I’ve created a login? What does it want? Flesh? Burnt offerings?
I load up the SQL Management Studio and notice that I can setup a login for the individual databases. This login setup screen is different. It goes on about schema. There’s about a dozen of them followed by a list of database role memberships.
I enter the login name, and select all the schema and click OK.
You must enter a name
I did! No wait, that was the login, there’s also another box. I add a name and click OK.
You cannot alter the schema ‘guest’. (Error: 15150)
I deselect the guest schema and click OK. No errors. So, back to aspnet_regsql, log in and a new error.
Invalid object name ‘sysdatabases’
<sigh>
Another search, find another MSDN blog which mention something about default databases. I launch the SQL Management Studio again, find the login I set up and change the default database to ‘master’. Click OK, back to aspnet_regsql.
HALLE – BASTARD – LUJA!
Excuse the vulgarities, but guess what? It worked!! Time for another jig round the room. All this trouble so it can populate a drop-down list from a list of databases running on the server. I select the database and click next to confirm, however it throws up another error.
Exception:
An error occurred during the execution of the SQL file ‘InstallCommon.sql’. The SQL error number is 262 and the SqlException message is: CREATE PROCEDURE permission denied in database.
This error is somewhat more vague than the rest and I had trouble finding anything relevant to what I was doing. I open up Management Studio yet again and look for anything to do with permissions. Nothing. I’m starting to get a bit tired of all this now. As a last ditch attempt, I go back to the aspnet_regsql wizard, and use windows authentication.
It works!!!! The wizard completes its tasks. I click finish and eagerly open up the Management Studio and look for my database. Sure enough there are a load of new tables. I am a database god!
Back to the ASP.NET video and Visual Studio and see what occurs next…
Leave the first comment ▶