Who needs SQL Server Enterprise Manager

I have downloaded the Microsoft SQL Server Desktop Engine to allow me to study for my MCSD (Which is going swimmingly at the present).

Anyway, when I installed it, I did as usual and selected the easiest install options available. It all worked as expected and I was accessing Northwind with ease.

However, one of the examples I was working on entailed returning a Dataset object of Customers details via a Web Service. Suddenly, I could no longer connect to northwind database.

My guess was that this was something to do with the fact that the Web Service runs under a different guise than the interactive user. So far all of my connection strings had used the "Integrated Security = SSPI", so I changed this to be the SysAdmin account and password that I had created when I installed MSDE.

My Web Service then returned the error "not associated with a trusted sql server connection". A quick pump of this error message into Google, informed me that I had to use the "SQL Server Enterprise Manager " tool and change the security model to be SQL Server and windows and not windows only.

I did not have SQL Server Enterprise Manager installed on my computer, I believe it is an optional download. I relayed this relatively (ZZzzz...) interesting story to a colleague at work, who subsequently reminded me that basically all of the settings were in the registry anyway and all we had to do was find them. After a quick (1 hour or so) trawl through regedit, we came across a key named "LoginMode" that was currently set to "1". We set this to "0" and SQL Server did not like it very much and refused to start, so we tried changing it to "2". This time the server started and it also recognised my SysAdmin user ID and password.

Full Key Spec is

HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/LoginMode

Obviously I cannot promote hacking the registry when there are controlled tools available to do it for you, but every now and then, it is good fun!