HOWTO Connect to MSDE from SQL Enterprise Manager

[tommyg]

First

You have to enable the named connection for the MSDE service.

Run C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe

Select tcp/ip and move to enabled protocol

Second

Run C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql -E

This will give you a trusted sql server connection where you can issue commands

Third

Type the following commands...

1> use master
2> EXEC sp_addlogin 'yourusernameyouwanttobeadmin','yourpasswd'
3> EXEC sp_addsrvrolemember 'yourusernameyouwanttobeadmin', 'sysadmin'
4> go

This should create a user with the name 'yourusernameyouwanttobeadmin' and with 'yourpassword' that you can use to register your SQL Server into SQL Enterprise Manager.

[aliya]

Can anyone let me know what commands should I use,if I want to let my client to connect to MSDE from SQL Enterprise Manager?

[su-sudo]

You can use following command in command prompt to connect...

osql -E -S <server_name>\<instance_name>

[Christina]

MSSQL Server has 2 authentication modes that is Integrated Windows, and Mixed
Integrated Windows + SQL Server, Microsoft is pushing Integrated Windows Authentication, rather than Mixed Mod. With IW Authentication, you need to grant the proper windows account permissions to login to the database give the login appropriate rights to the databases in question connect to SQL Server using that Windows context.

[bill mates]

The main advantage of MSDE databases are it is fully compliant with SQL server technology, that is, the tables, stored procedures, triggers or any other database objects created in MSDE will be operated without modification in the SQL server database. So it offers full features of SQL server.