SQL Authentication Mode

[visualsi]

I would like to check to see the SQL Server that a user selects has "mixed mode authentication" selected. I found a registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer and they key is LoginMode.

A value of 1 is "Windows Only" and 2 is "Mixed Mode". The problem is that the value in the middle of th ekey is loosely based on a combination of the version of SQL they are using and the name of the instance. I want to add this as a check in the disalog sequence after they select the SQL Server and if the value is not 2, give the person a warning and allow to cancel and try again.

Any suggestions?

Thanks,
Rob

[ales.hrodek]

Middle part you can get from HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
There is one value for each installed instance so you can check all values located here.

If you need list of installed instances value is here: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances

[visualsi]

Hi,

Thanks, but that's only part of the variable part. Plus, this needs to be fit into a format that can be formed into validation expression. I think there's still a long distance between the two.

Thanks,
Rob

[CiprianComsa]

Hi Rob,

As ales.hrodek said , a solution would be to use a search to identify the name of the instance.
The name of the instance can be identified in the registries from the following path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
The name of the instance will be saved in a property, which is actually the search name.
You can then use the result in a vbs custom action to identify the LoginMode.

You cannot use 2 searches because windows installer executes them randomly.

Regards,
Ciprian
__________________________________________________________________________________________________________________________________________________
Ciprian Comsa
Advanced Installer Team
http://www.advancedinstaller.com/

[visualsi]

Hi,

That would be a lot easier if the SQL Server was installed locally on the machine where the install was being done.

In almost all cases, our customers do the install on an application server and they are not allowed to log into the console of the database server (where the registry is that would have that information). They are sometimes given credentials that don't work for one reason or another and we need to find out why, so we can tell them more than just "your credentials are bad".

One of the typical culprits is that the SQL Server isn't set up for mixed mode authentication. We need to test for this. If the SQL Server was installed locally, they could likely go into the SSMS and look at the setup fairly easily (or we could write a program to look at the registry). I believe that looking at a "secured" enterprise database server's registry is going to be a bit more difficult.

Having this check could reduce a 2-15 day install to a 30 minute procedure by replacing politics, form filling and departmental feuding with a simple check that produces a easy to read screen shot. It is important. Having this (built into) in a future release would be greatly appreciated.

[mihai.petcu]

Hello,

I believe that looking at a "secured" enterprise database server's registry is going to be a bit more difficult. Having this (built into) in a future release would be greatly appreciated.

As you mentioned yourself, I'm afraid there doesn't seem to be a way to accomplish this through a secure server.

All the best,
Mihai