I was configuring BTS 2006 R2 on a machine. After I ran the configuration wizard I got an error saying that “login failed for user on SQL server. Cannot create database SSO”.

The logical resolution was to go to SQL Server and give the specified user the appropriate permissions; which I did. I re-ran the configuration again and again I got the same error.

Puzzled for a couple of minutes, I checked the event viewer and there it was: I found the exact error reason:

“Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SSODB.mdf’ because it already exists. Change the file path or the file name, and retry the operation.”

It turned out that I had a previous installation of the SSO database on the same machine, and it was not deleted automatically (for some reason which I cannot remember frankly). So I manually deleted the “mdf” and “ldf” files, re-ran the configuration, and this time it worked fine.

Moral of the story: some error messages can be misleading. Always check the event viewer when working with BizTalk Server because many times the real reason of a problem will only be found there.