SQL Server: enable remote connections without SSMS

Question

I've got a SQL Server Express 2008 install on my web server, which by default does not allow remote connections (probably a good thing.) I opted not to install SQL Server Management Studio Express along with it for disk space and other reasons.

I need to enable remote connections but any instructions I can find involve using SSMS to change that setting. Surely there's a transact-sql statement of some kind i can from from sqlcmd.exe to change the setting?!

Thanks!

Answer 1

You don't need SSMS, just the SQL Server Configuration Manager as detailed here.

Answer 2

Even without installing Management Studio, there should be a SQL Configuration tool in your start menu (Start > Programs > SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager). You can use this tool to enable remote connections:

• Expand SQL Server Network Configuration
• Expand Protocols for your instance (SQLEXPRESS most likely)
• Then enable TCP/IP in the protocols.
• Under the TCP/IP properties, you may want to control which interfaces and ports it listens on in the "IP Addreses" tab.

Also note that changes to this configuration will require a restart of the SQL Server service, and when you set the port to listen on (the default is 1433), you will need to create rules in any firewall you may have running to allow the communication. Good luck!

Answer 3

I went into my SMS and my local instance properties -> Connections -> checked "Allow remote connections to this server" and scripted the change.

EXEC sys.sp_configure N'remote access', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO