I have set up a SQL Server 2005 to use for testing before moving to another production server. The server works fine when I connect to it with a program on the same computer but not when accessed remotely.
I have set it to to allow remote connections and use TCP IP only and have opened TCP port 1433 on the Windows XP firewall and am using the Teredo service on Port UDP 3544 Also the Server Browser service is running. The server is connected directly to the internet on a static IP (eg. 67.156.18.167) I have set the server to Windows authentication.
When I try to connect to the database from a data grid in Visual Studio on another computer it fails.
I am pretty surethat the problem is that I am not giving the correct information when asked to fill in the information for the data source to find the server as I am new to remote connections in SQL Server.
Thanks for any insightHow are you trying to authenticate the remote system? I could be wrong, but I was of the impression that Windows authentication was only for local access, while remotely you need to be using SQL Server authentication with login / password.|||
Hi,
Could you please provide the full error message and the connection string? Also, please take a look at this tutorial on enabling remote connections: http://support.microsoft.com/default.aspx/kb/914277
Thanks,
Il-Sung.
|||I did look at the tutorial on remote connections and made the suggested additions.
I then tried to connect but still received erorrs. I will include them below. Is this possibly happening because I originally set up the server for Windows Authentication not Mixed Mode. If so is there a way I can reset it Mixed Mode Authentication now. I can't seem to locate any way.
--
The name of the server and instance is something like rawtech-7d1b544
The computer Static IP is something like 64.123.16.142
User name is UB using Windows Authentication
Table name in the database is Players
Tried this connection string -
Data Source=64.123.16.142;Initial Catalog=Players;Integrated Security=True
Got this error message -
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Tried this connection string -
Data Source=64.123.16.142\rawtech-7d1b544;Initial Catalog=Players;Integrated Security=True
Got this error message -
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Tried this connection string -
Data Source=vantage-7d1b669;Initial Catalog=Players;Integrated Security=True
Got this error message -
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Thanks for the help
|||If you are connect to named instance, "Data Source=64.123.16.142\rawtech-7d1b544" is correct. the error message indicates that sqlbrowser is not on or can't be contacted by the client driver. Please make sure sqlbrowser is on you can use SQL Server Surface Area Configuration to do so, besides, you also need to make a firewall exception for udp port 1434. Also make sure remote protocol is enabled and TCP port number, if it means to be enabled and it is different from default port 1433 if it is name instance (shown in errorlog), is unblocked by firewall setting. If you are still not be able to conneciton, try the guildline on in this forums for step by step testing of your connectivity and post which step actually fails, I can give more specific instruction on how it can be resolved.
Let me know if this helps.
|||SQL Browser is running. Made a general except on port UDP 1434
Remote protocal is enabled as TCP/IP only Made exemption in firewall to port TCP 1433
Also still using Windows Authentication
Same error messages.
Thanks Ush|||Ush, if your instance is a named instance, the TCP port normally is different from TCP port (1433) of a default instance. Please take a look in the error log to see what is the port number, or use sql server configuraiton manager to see server side configuration. -HTH|||Hi Ush,
I would recommend setting a firewall exception for the sqlservr.exe executable as the KB article recommends. Then you don't need to worry which port your named instance is listening on.
Il-Sung.
|||Done but still no success
|||Ush,
Can you try this connection string and let me know the error message.
"Data Source=tcp:64.123.16.142, tcp_port_number;Initial Catalog=Players;Integrated Security=True"
|||I tried this connection string
Data Source=tcp:64.123.16.142,1433;Initial Catalog=Players;Integrated Security=True
And this was the error message
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond
after a period of time, or established connection failed because connected host has failed to respond.)
1. Are you sure that your named instance is listening on 1433, the default port. In most cases, it doesn't unless you explicitly specify to do so.
2. this error message indicates, in most cases, that your firewall is blocking your connections.
-HTH
sqlsql
No comments:
Post a Comment