assaadrezk
asked on
How to detect that SQL Server is installed in Local Machine
how to detect that SQL server or MSDE is installed on the Local Machine
I want to do that in my program using VB.NET
thanks
I want to do that in my program using VB.NET
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How about searching for the
HKLM/Software/Microsoft/Mi crosoft SQL Server key in the registry?
Or search program files directory for sqlservr.exe
HKLM/Software/Microsoft/Mi
Or search program files directory for sqlservr.exe
ASKER
good idea, you have the code doing that
First, check for the existence of this key:
HKEY_LOCAL_MACHINE\SYSTEM\ CurrentCon trolSet\Se rvices\MSS QLSERVER
OR
HKEY_LOCAL_MACHINE\SYSTEM\ CurrentCon trolSet\Se rvices\MSS QL$[INSTAN CENAME]
Look at the "ImagePath" string entry under this key. It will point to the SQL Server service
If this key exists, then you can then try to connect to it using:
Data Source=(local);Integrated Security=SSPI;Connect Timeout=15;
OR
Data Source=(local)\INSTANCENAM E;Integrat ed Security=SSPI;Connect Timeout=15;
NOTE: you may need to replace the word (local) with the actual machine name, in some rare cases, (local) does not resolve because people have changed their local DNS mapping.
If this timesout then one of the following could be wrong:
1: The MSSQLSERVER service is not running or
2: the current user is not a member of the administrator group, or any other group which has login permissions on the sql server. NOTE: in this case, you will get an access denied.
3: The sql server service is running, the user does have permissions but is currently logged into a computer using cached network credentials (the network cable has been unplugged) and their access to the SQL Server is based on a network set of credentials. SQL Server does not cache credentials unlike Windows, which will allow you to login to a machine off network (as long as you have logged int before).
4: The maximum number of connections has been exceeded.
For MSDE, you will need to search for all keys that begin with MSSQL$ under this hive:
HKEY_LOCAL_MACHINE\SYSTEM\ CurrentCon trolSet\Se rvices\
Same deal applies here, you could get denied access for any of the above reasons. The only way I know how to determine the difference between a full blown SQL Server and an MSDE instance is to call: xp_msver
HKEY_LOCAL_MACHINE\SYSTEM\
OR
HKEY_LOCAL_MACHINE\SYSTEM\
Look at the "ImagePath" string entry under this key. It will point to the SQL Server service
If this key exists, then you can then try to connect to it using:
Data Source=(local);Integrated Security=SSPI;Connect Timeout=15;
OR
Data Source=(local)\INSTANCENAM
NOTE: you may need to replace the word (local) with the actual machine name, in some rare cases, (local) does not resolve because people have changed their local DNS mapping.
If this timesout then one of the following could be wrong:
1: The MSSQLSERVER service is not running or
2: the current user is not a member of the administrator group, or any other group which has login permissions on the sql server. NOTE: in this case, you will get an access denied.
3: The sql server service is running, the user does have permissions but is currently logged into a computer using cached network credentials (the network cable has been unplugged) and their access to the SQL Server is based on a network set of credentials. SQL Server does not cache credentials unlike Windows, which will allow you to login to a machine off network (as long as you have logged int before).
4: The maximum number of connections has been exceeded.
For MSDE, you will need to search for all keys that begin with MSSQL$ under this hive:
HKEY_LOCAL_MACHINE\SYSTEM\
Same deal applies here, you could get denied access for any of the above reasons. The only way I know how to determine the difference between a full blown SQL Server and an MSDE instance is to call: xp_msver
Actually I do. Below is a class that I wrote in VB.NET 2005 to do registry queries. It is tailored to what I was trying to accomplish, but should give you an idea. Obviously I retain all rights to this code in terms of it still being mine, but grant readers the right to use it:
Imports Microsoft.Win32
Public Class RegSearchItem
Private DefaultValue As String
Public PropertyName As String
Public RegValueName As String
Public Value As String
Public RegHive As RegistryHive
Public SubKey As String 'not including hive should be like Software\Microsoft\Windows or something
'PropertyName allows you to give it a different value to write to the XMLAttribute than what it was in the registry
'RegValueName, must be the name of the value you are looking for in the registry
'if you don't specify PropertyName, it is =RegValueName
Sub New(ByVal RegHive As RegistryHive, ByVal SubKey As String, ByVal RegValueName As String, Optional ByVal PropertyName As String = "", Optional ByVal DefaultValue As String = "")
Me.RegValueName = RegValueName
Me.SubKey = SubKey
Me.RegHive = RegHive
If PropertyName = "" Then
Me.PropertyName = RegValueName
Else
Me.PropertyName = PropertyName
End If
Me.DefaultValue = DefaultValue
End Sub
Sub QueryRegistry()
Dim key As RegistryKey
'get the sub key from the given hive
Select Case RegHive
Case RegistryHive.ClassesRoot
key = My.Computer.Registry.Class esRoot.Ope nSubKey(Su bKey)
Case RegistryHive.CurrentConfig
key = My.Computer.Registry.Curre ntConfig.O penSubKey( SubKey)
Case RegistryHive.CurrentUser
key = My.Computer.Registry.Curre ntUser.Ope nSubKey(Su bKey)
Case RegistryHive.DynData
key = My.Computer.Registry.DynDa ta.OpenSub Key(SubKey )
Case RegistryHive.LocalMachine
key = My.Computer.Registry.Local Machine.Op enSubKey(S ubKey)
Case RegistryHive.PerformanceDa ta
key = My.Computer.Registry.Perfo rmanceData .OpenSubKe y(SubKey)
Case RegistryHive.Users
key = My.Computer.Registry.Users .OpenSubKe y(SubKey)
Case Else
key = Nothing
End Select
If Not key Is Nothing Then
'get the value of the given property, under this subkey
Value = key.GetValue(RegValueName)
key.Close()
Else
Value = DefaultValue
End If
End Sub
End Class
HTH,
Shane
Imports Microsoft.Win32
Public Class RegSearchItem
Private DefaultValue As String
Public PropertyName As String
Public RegValueName As String
Public Value As String
Public RegHive As RegistryHive
Public SubKey As String 'not including hive should be like Software\Microsoft\Windows
'PropertyName allows you to give it a different value to write to the XMLAttribute than what it was in the registry
'RegValueName, must be the name of the value you are looking for in the registry
'if you don't specify PropertyName, it is =RegValueName
Sub New(ByVal RegHive As RegistryHive, ByVal SubKey As String, ByVal RegValueName As String, Optional ByVal PropertyName As String = "", Optional ByVal DefaultValue As String = "")
Me.RegValueName = RegValueName
Me.SubKey = SubKey
Me.RegHive = RegHive
If PropertyName = "" Then
Me.PropertyName = RegValueName
Else
Me.PropertyName = PropertyName
End If
Me.DefaultValue = DefaultValue
End Sub
Sub QueryRegistry()
Dim key As RegistryKey
'get the sub key from the given hive
Select Case RegHive
Case RegistryHive.ClassesRoot
key = My.Computer.Registry.Class
Case RegistryHive.CurrentConfig
key = My.Computer.Registry.Curre
Case RegistryHive.CurrentUser
key = My.Computer.Registry.Curre
Case RegistryHive.DynData
key = My.Computer.Registry.DynDa
Case RegistryHive.LocalMachine
key = My.Computer.Registry.Local
Case RegistryHive.PerformanceDa
key = My.Computer.Registry.Perfo
Case RegistryHive.Users
key = My.Computer.Registry.Users
Case Else
key = Nothing
End Select
If Not key Is Nothing Then
'get the value of the given property, under this subkey
Value = key.GetValue(RegValueName)
key.Close()
Else
Value = DefaultValue
End If
End Sub
End Class
HTH,
Shane
ASKER