Thursday, August 4, 2005

1217.aspx

How to enable remote access to MSDE

I use Microsoft SQL Server Desktop Engine 2000 for all my personal projects including the blogs. It is free and comes with the most important features of SQL Server like views, stored procedures, backup etc. It has some limitations in terms of performance when there are many parallel queries but that is not a problem for my small projects. The only thing that bugs me is that it cannot be administered from remote.


Or, it used to bug me, until I found the trick that allows remote access and administration of MSDE:



  • Click Start, Run

  • Enter svrnetcn.exe too run the "SQL Server Network Utility"

  • Select "TCP/IP" from the Disabled protocols and click "Enable >>“ as the image below shows


Or if you prefer to do it the difficult and dangerous way; import these registry settings:



[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib]
"ProtocolList"=hex(7):74,00,63,00,70,00,00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp]
"TcpHideFlag"=dword:00000000
"TcpDynamicPorts"=""
"TcpPort"="1433"


That's it!


You can now access MSDE remotely with SQL Server Enterprise Manager, Microsoft Access or one of the free tools like MSDE Query Tool

6 comments:

  1. Thank you, its really work!

    ReplyDelete
  2. Quick note -- don't forget to also allow SQL Server connections (1433 by default) through the firewall if you have it enabled.

    ReplyDelete
  3. Thanks a lot! Short and useful! :)

    ReplyDelete
  4. Here is your annual thanks for posting this.

    Much appreciated!

    ReplyDelete
  5. Thanks a lot!

    You saved my time.

    ReplyDelete