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

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

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

@crizCraig's answer helped me getting it to work.

I'll describe in short what I did, to successfully execute the cmd.

config: 2 instances of SQL // SQL2005 + 2008 Express instance on the remote machine, which refuses to install Management Studio 2008.

1. Enable TCP/IP Protocol
2. Enable Named Pipes
3. started sqlcmd Utility with -s \SQLEXPRESS to get the right sever-instance C:\Program Files\Microsoft SQL Server\100\Tools\Binn
4. executed @crizCraig's - sqlcode

Here are the links I got the info from: http://msdn.microsoft.com/en-us/library/ms162773.aspx http://msdn.microsoft.com/en-us/library/ms162816.aspx