Before enabling remote connections in SQL Server 2008, ensure:

• SQL Server service is running

• SQL Server Browser service is running (for named instances)

• Appropriate Windows permissions are granted

Run SQL Server Configuration Manager:

1. Navigate to SQL Server Network Configuration → Protocols for [INSTANCE_NAME]

2. Right-click TCP/IP and select Enable

3. Restart SQL Server service

-- Verify TCP/IP status via T-SQL
SELECT local_net_address, local_tcp_port 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

For default instance (MSSQLSERVER):

1. In Configuration Manager, open TCP/IP Properties

2. Under IP Addresses tab, clear TCP Dynamic Ports

3. Set TCP Port to 1433 (or custom port)

4. Restart SQL Server service

Create inbound rules for:

• SQL Server executable (sqlservr.exe)

• TCP port 1433 (or your custom port)

• UDP port 1434 (for SQL Browser)

# PowerShell command to create firewall rule
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

1. Connect via SSMS

2. Right-click server → Properties

3. Select Connections page

4. Check Allow remote connections to this server

Connection timeout: Verify network connectivity and firewall rules

Login failed: Ensure SQL authentication is enabled

Port not listening: Check with netstat -ano | findstr 1433

• Use strong passwords for SA account

• Consider enabling encryption

• Restrict access via IP address filtering

• Regularly update SQL Server with latest patches

Enabling remote connections in SQL Server 2008 involves more than just opening a firewall port. The process requires configuration changes at both the SQL Server instance level and the Windows OS level. Here's what you need to verify:

First, you need to configure SQL Server to accept remote connections:

-- Connect to the SQL Server instance using SSMS
-- Right-click on the server name and select Properties
-- Go to the Connections page
-- Check "Allow remote connections to this server"
-- Click OK to save changes

SQL Server must be configured to use TCP/IP protocol:

1. Open SQL Server Configuration Manager
2. Navigate to SQL Server Network Configuration
3. Select "Protocols for [YourInstanceName]"
4. Ensure TCP/IP is enabled
5. Right-click TCP/IP and select Properties
6. Under IP Addresses tab, verify all IPs have TCP Port 1433 enabled
7. Restart the SQL Server service

While you've already created a rule for port 1433, let's ensure it's properly configured:

# PowerShell command to create firewall rule
New-NetFirewallRule -DisplayName "SQL Server TCP 1433" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

If you're using named instances, the SQL Browser service must be running:

sc query SQLBrowser
sc config SQLBrowser start= auto
net start SQLBrowser

If you're still having connection problems, try these diagnostic steps:

-- Check if remote connections are truly enabled
EXEC sp_configure 'remote access', 1;
RECONFIGURE;

-- Verify TCP/IP is listening on the correct port
SELECT local_tcp_port FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

When enabling remote access, always consider security:

• Use strong passwords for all SQL logins
• Consider changing the default port from 1433
• Implement IP restrictions where possible
• Enable encryption for remote connections

For environments where direct TCP/IP connections aren't possible, consider:

-- Using Shared Memory for local connections
Server=localhost;Integrated Security=true;

-- Using Named Pipes if TCP/IP is blocked
Server=np:servername\pipe\sql\query;Integrated Security=true;