SQL Server Enterprise Manager

[hpl]

I am using Win XP and have installed MSDE. How and where do I find the SQL Server Enterprise Manager, where I can change the security settings?

[Memnoch1207]

MSDE does not come with the Enterprise Manager user interface...in order to perform the activities you are asking you would need to upgrade to sql server...you could try this (I have never used it) it may achieve want you are wanting.

[hpl]

Is it possible to download this ASPEnterpriseManager? I have looked all places but didn't had any luck on finding it.

[Memnoch1207]

you can go here

The server has to be accessible over the internet...my assumption is that your MSDE is not accessible over the internet though...Therefore this won't help. about the only other thing you could do is learn osql and isql...

[hpl]

Ok, thanks a lot. Maybe the best thing would be to install SQL Server 2000?

[Jackalx25]

You can change security settings using SQLDMO ie. from mixed to windows only. I have some code that does that if you need it.

SQLDMO examples and help are provided with SQL Server.

Also, if you want to check out a free MSDE managment tool with all source code you might want to check out :

http://www.asql.biz/DbaMgr.shtm

The source code is a bit rough but once you sift through to what you want, it is a good guide to help developing your own MSDE "Enterprise Manager".

Cheers

Jack

[hpl]

Great it is just what I need. I need some code that sets the Authentication to "SQL Server and Windows", do you have that code? I'm running an ASP.Net application with the MSDE.

[Jackalx25]

Sorry...posted to early..see next post

[Jackalx25]

Am not sure about ASP.net, this code is from a VB6 application, modified and stripped of error handling to make it a bit easier to follow.

Here it is anyhow, might still be useful.

First, set a reference to Microsoft SQLDMO Object Library.

The steps are:

1. Connect to the instance of SQL Server or MSDE in this case
2. Change the intergrated security level
3. Stop and restart the server
4. Disconnect and clean up

Here is the code:

Option Explicit
Private mobjServer As SQLDMO.SQLServer
Private Const strServ as string = "(local)"  'your server name  

Private Sub cmdConnect_Click()  Step 1 - Connect 
Set mobjServer = New SQLDMO.SQLServer
mobjServer.Name = strServ
With mobjServer  ' Connection method 1. if using windows authentication
   .LoginTimeout = -1
   .LoginSecure = True
   .AutoReConnect = False
   .Connect strServ
End With
  ' OR Connection method 2. if using SQL Server authentication
With mobjServer    
    .LoginTimeout = -1
    .LoginSecure = False
    .AutoReConnect = False
    .Connect strServ, "Username","Password"
End With
Msgbox "Connected"
End Sub

Private Sub cmdChangeMode_Click()  Step 2 - Change intergrated security mode 
With mobjServer
.IntegratedSecurity.SecurityMode = 2  1 = Windows only, 2 = Mixed mode 
.Disconnect  Step 3 - Stop and restart server so changes are made 
.Stop
End With
 'You need to put a timer or loop in before you re-start as it takes a few seconds for 
the server to stop
timStatus.Interval = 2000
timStatus.Enabled = True
End Sub

Private Sub timStatus_Timer()
CheckState
End Sub

Private Sub CheckState()
Dim eStatus As SQLDMO_SVCSTATUS_TYPE
eStatus = mobjServer.Status
Select Case eStatus
     Case SQLDMOSvc_Stopped
     Msgbox "Stopped" 
     mobjServer.Start True, strServ 
       When TRUE, an attempt is made to connect on successful start. 
                        When FALSE, no attempt is made to connect after successful start.  
     Case SQLDMOSvc_Running
     timStatus.Enabled = False
     Msgbox "Started"       
End Select
End Sub

 You find out the security mode when connected using;
Private Sub cmdCommand1_Click()
     Msgbox mobjServer.IntegratedSecurity.SecurityMode
End Sub

 Step 4 - Disconnect and clean up 

mobjServer.Disconnect
Set mobjServer = Nothing

Cheers

Jack