Wednesday, July 1, 2009

Microsoft SQL Server 2005 Express installs without SQL Authentication

First, lets give some unnecessary background information :-)

For a project I'm working on I'm taking a look at a business processes and especially systems that support modeling such processes. The Business Process Modeling Initiative (BPMI) has come up with a nice standard notation for such models, which of course is then called the Business Process Modeling Notation (BPMN), and is being managed by OMG (they also have CORBA and other interesting stuff). My project is about researching development software that supports such modeling (especially using BPMN) and then takes the modelled process directly into further development and finally results in some kind of software artifacts, like for example a website.

How did I get to the problem? During this work, I was installing one such development software (the BizAgi Studio), which requires SQL Server to be installed. However, the installation wizard does not install the SQL server so I had to figure out what to do. Because I just wanted to take a look I decided to install Microsoft SQL Server 2005 Express on my local machine an get going on that one. The SQL Server 2005 Express comes on my Microsoft Visual Studio 2008 Standard Edition DVD so I got it installed without a problem.

So, what is the problem? The thing is, when BizAgi asked for information on my SQL Instance it just allowed for SQL Authentication and that is where my problem started. I had just installed the SQL Server Express and I was never asked to provide the password of the infamous 'sa' user so I had no idea what it was. After some Google work I found the reason: SQL Authentication is not allowed by default when installing the SQL Server Express.

Now, how do you solve that?

There really are no tools that come with the SQL Server Express so there is no way to configure anything. This is why some solutions found by Google refer to using RegEdit, changing [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer] to '2', restarting the SQL service and then run an SQL script to create the SQL Authentication user. However, in order to run such SQL scripts you might want to download the Microsoft SQL Server Management Studio Express.

However, if you have Management Studio installed then why use the RegEdit, just right-click your server, select Properties, select Security, select SQL Server and Windows Authentication and click OK. Then enable the SQl Authentication User by right-click on it, select Properties, select Status, select Enabled and click OK. You are ready, try it now. It worked for me! No RegEdit!

Thought I would share this with you. Could help someone else.