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!

Eccles

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

Zuzana

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!

Gower

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

Visby

sqlcmd parameters are case-sensitive, and the instance name requires a machine name, or . for the local machine. To connect to the local named instance, try sqlcmd -S .\SQLEXPRESS. – Hassi

Answer 4

On SQL Express 2014 none of the answers worked. This is what worked for me:

1. You need to enable Named Pipes and TCP/IP in SQL Server Configuration Manager and restart SQL Server instance.
2. Also in SCM, TCP/IP properties, IP Addresses tab: I enabled all of them. And the last one: TCP Port: 1433
3. Restart SQL Server instance.
4. You need to open port TCP 1433 and port UDP 1434 on both computers' firewalls for in and out.
5. On my computer sqlcmd is in C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\
6. sqlcmd -S 192.168.1.55,1433 -U sa -P password
7. To check remote connections settings: sp_configure 'remote admin connections' (and press enter)
8. go (and press enter)
9. sp_configure 'remote admin connections', 1 (and press enter)
10. go (and press enter)
11. reconfigure (and press enter)
12. go (and press enter)
13. To check remote connections settings again: sp_configure 'remote admin connections' (and press enter)
14. go (and press enter)
15. quit (and press enter)
16. Restart SQL Server instance.
17. Connect using SSMS with Server: computername\instancename Authentication: SQL Server Authentication Username: sa Password: password

Mastery