Connecting to remote SQL Server 2005

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

Connecting to Linked Servers

I know this is an easy one for most of you but this is new territory for me.

I am trying to link a production application database to an existing SMS database.

Both of these databases are SQL 2000. I have sa access to both of them.

I have an accreditation application that deals with internal processes within our active directory network. We are using SMS database collections to provide the discovery for reporting of this application database and I want to compare the collection to the existing data between the 2 databases.

For simplicity I will define them both.

Application Database we will call “A-Database”

SMS Database we will call “B-Database”

I have gone into the A-Database and gone to linked Servers under the security node in enterprise manager. I created a new linked database and selected the SQL Server option in the general Tab.

In the Security Tab I selected the “Be made using the security context” and used “sa” for the Remote login and the CORRECT sa password for the With password option.

The server options tab remain the same with ”RPC, RPC Out, & Use Remote Collation” selected.

It appears to connect but there is not any Table or Views showing up. What am I doing wrong here and what do I need to do to link these 2 database engines so I can move forward?

Thanks,

Gene Godsey

In the properties of the Linked Servers I am looking at trying to change my connection by selecting the general tab and selecting the option for Other Data Sources. I have selected the Microsoft OLE DB Provider for SQL Server and then I have to provide data for 3 fields.

In the Product Name field I just put the database name I want to use from the server. In the Data Source name I insert serverName\DataSource name.

The Privider string has me stumped.

The location is grayed out but I believe that this will open up when I have a provider name. I do not know what goes there either.

It says that the catelog name is optional. I am not worried about that field.

Any suggestions.

Thanks,

|||Hi Geneway,

Im not sure why you are putting the DB name in the Product Name? I dont understand that bit. Where is it saying Product Name?

Your datasource would be the server name.

Your catalog would be the database.

Keep us updated.

Barry Andrew (Pace)|||

In the enterprise manager of SQL 2000 go to the security node. Under this you would go to the linked servers. If there is a linked server I would right click and select the properties link.

When the properties dialog box opens there are 3 tabs. General/Security/Server Options.

I select the General tab and the\n I will select the “Other Data Source” option.

From here I would select the “Microsoft OLE DB Provider for SQL Server” Dropdown. At this point there are 3 text boxes that are available to insert data.

1: Product name:

2: Data Source:

3: Provider String:

I must have this information incorrect because I cannot see tables or view from this.

I have tried to select the SQL Server option but I cannot control what database that is selected in that option.

Any suggestions,

Thanks,

|||Hi,

Do as you have been doing and use the following;

1) SQL Server - this will do fine
2) NetworkNameHere!!! - IE the server name the sql database is running on
3) a) Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
b)Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

A and B are trusted and standard authentication methods respectively

You may also find the website; www.connectionstrings.com useful.

hth

Pace|||

I figured it out!

YEA!!!!!

I created an datareader account in the SMS database. I selected the connect to SQL Server option button in the general tab location.

Under security I selected the sa account but the remopte user was the datareader account that I created in the SMS Database. It was really easy but this is not very well documented.

Connecting Surface Area Configuration to server on Clustered Server

I've run into a problem after upgrading production servers running under Microsoft Cluster Server, Windows Enterprise Server 2003 SP1, and SQL Server 2005 SP1 Enterprise Edition.

Once I completed the upgrade and applied the patch, I needed to examine the values set in the Features component of the Surface Area Configuration tool. The tool attempts to connect to localhost\instancename, but reports the following error:

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)(Microsoft SQL Server)

I'm logged into the virtual server via Remote Desktop, but the SAC tool thinks I'm logged into the physical server supporting the virtual server. I seem to be caught between a rock and a hard place here, as I can't enable remote connections without first gaining access on the server itself.

Any help will be greatly appreciated.

Can anyone give me a little help here - I can't make configuration changes on production servers.

Thanks.

|||

Well, after a lucky break I found the answer.

sp_configure 'remote admin connections', 1
go
reconfigure
go

Once this was executed on the server I was able to go into Surface Area Configuration, enter the virtual server name, and connect successfully and make the other changes necessary. A simple solution to a scary problem. (Even though I'd used sp_configure for years on both Sybase and MSSQL servers it's easy to forget about the simple solutions.)

Connecting Surface Area Configuration to server on Clustered Server

I've run into a problem after upgrading production servers running under Microsoft Cluster Server, Windows Enterprise Server 2003 SP1, and SQL Server 2005 SP1 Enterprise Edition.

Once I completed the upgrade and applied the patch, I needed to examine the values set in the Features component of the Surface Area Configuration tool. The tool attempts to connect to localhost\instancename, but reports the following error:

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)(Microsoft SQL Server)

I'm logged into the virtual server via Remote Desktop, but the SAC tool thinks I'm logged into the physical server supporting the virtual server. I seem to be caught between a rock and a hard place here, as I can't enable remote connections without first gaining access on the server itself.

Any help will be greatly appreciated.

Can anyone give me a little help here - I can't make configuration changes on production servers.

Thanks.

|||

Well, after a lucky break I found the answer.

sp_configure 'remote admin connections', 1
go
reconfigure
go

Once this was executed on the server I was able to go into Surface Area Configuration, enter the virtual server name, and connect successfully and make the other changes necessary. A simple solution to a scary problem. (Even though I'd used sp_configure for years on both Sybase and MSSQL servers it's easy to forget about the simple solutions.)

|||thx a lot