Changing MSDE Authentication Scheme After Installation

If you are using Mi­crosoft SQL Server 2000 Desk­top En­gine (MSDE 2000) you are sup­posed to de­cide if you are going to use “in­te­grated win­dows au­then­tifi­ca­tion” only or if you are using “mixed mode au­thet­i­ca­tion”. Lat­ter is some­times con­sid­ered less se­cure but if you are de­vel­op­ing ASP.​NET ap­pli­ca­tions it can be eas­ier to use a non-NT user for the con­nec­tion.

If you ever tried that you are surly fa­mil­iar with the “login is not as­so­ci­ated with a trusted con­nec­tion” ex­cep­tion when try­ing to ac­cess the data­base. Today I had to in­stall an ASP.​NET ap­pli­ca­tion on a server with MSDE where mixed mode au­then­ti­ca­tion was not avail­able. A quick re­search on the net re­vieled a blog entry in­di­cat­ing how to change the au­then­ti­ca­tion scheme of MSDE after the in­stal­la­tion.

  • Stop the MSDE ser­vice
  • Search the reg­istry for

    HKEY_LO­CAL_­MA­CHINE\Soft­ware\Mi­crosoft\MSSqlserver\MSSqlServer

    (for un­named in­stances) or

    HKEY_LO­CAL_­MA­CHINE\Soft­ware\Mi­crosoft\Mi­crosoft SQL Server\In­stance Name\MSSQLServer\

    (for named in­stances)

  • Change the key LoginMode to value 2.

Un­like a com­ment on the page, value 0 will not work (at least it didn’t in my case).