February 11, 2012 at 8:48 am
SQl Server newbie, so please be gentle.
New install of SQL Server 2008 Express using default values on a Windows Server 2008 R2 Standard box.
Remote Connection enabled, as detailed here[/url], working as expected.
SQL Server(SQLEXPRESS) running, SQL Server browser running.
No mods to firewall.
Logged on as Administrator with Guest account enabled for a shared folder on drive C:\, no other roles defined.
Attempting to logon to server 'SERVER2008R2' using 'With Windows NT authentication using the network login ID'.
Error message for both 32 and 64 bit clients almost identical:-
Connection failed:
SQLState: '01000'
SQL Server Error:2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error:17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Also tried attempting to logon to server 'SERVER2008R2\SQLEXPRESS' with similar error message.
Thanks in advance
February 12, 2012 at 7:19 am
Check to see if the account you are using for the DSN has permissions on SQL Server. You might also want to check the SQL Server error log to see if connection failures show up in there.
Other than that attempt a telnet session to the port that SQL Server is listening on to confirm that you aren't being blocked by any firewalls.
February 12, 2012 at 8:30 am
Nicholas
Thanks for your reply.
Don't understand 'Check to see if the account you are using for the DSN has permissions on SQL Server.'
Nothing to be found in the SQL Server Error Log.
Am accessing SQL Server R2 Express on the Windows Server as Administrator, but have tried adding a SQL Server user 'chris' + password and am able to logon using 'Sql Server authentication'.
Have since opened the fiewall ports as detailed here
Still unable to access via the Win 7 clients regardless of Authentication method.
February 12, 2012 at 8:35 am
When creating a DSN and choosing Windows Authentication for your login id it will pass along the credentials of the account that you are logged in as. If that account does not have permissions to connect to SQL Server then you won't be able to login. That may be one of the reasons that it connects just fine when you use a SQL Server account.
February 12, 2012 at 9:42 am
Seems the firewall is the problem.
With the firewall OFF, you can access the SQL Server 2008 R2 Express via SQL Server authentication.
Yet the script suggested by Microsoft:-
@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE
has not allowed access.
Any suggestions as to how to configure the firewall to allow access?
February 12, 2012 at 9:48 am
If SQL Server isn't listening on 1433 then there would be no firewall exception put in place for it.
UDP 1434 allows the browser service to tell the client what port SQL Server is listening on. If you aren't listening on the default 1433 (which a named instance wouldn't unless hard set) then you would actually end up with a random port, which would then be blocked by the firewall.
It's always best to set a port using the SQL Server Configuration Manager. You might also want to check and confirm that SQL Server is listening on TCP/IP and not just named pipes.
Go to the SQL Server Configuration Manager -> SQL Server Network Configuration and ensure that TCP/IP is enabled. You can also go into the properties there and hard set a port for SQL Server.
February 13, 2012 at 1:58 am
Nicholas
Hard coding port 1433 resolved the issue.
Many thanks for your replies.
Best wishes
Chris
February 13, 2012 at 8:55 am
Excellent.
Firewalls, they are great, but they can be a real pain too.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply