Connecting to Remote SQL Server

Hi all,

I have been encountering problems in connectiing to an sql server remotely. I have a SQL Server installed on a machine with a public IP. I am, running an application on one of my other Servers having a Public IP as well. Now i want to access the SQL Server Database of my first machine from my second machine, but i cannot get connected to it. I give the IP address of the machine in the connection string but it fails. I have also tried it with the port number. What could possibly be the solution for a problem like this ?

Regards.

Hi there,

Maybe It caused by your windows firewall ! the initial status of Windows Firewall is on.
Check the status by turning off your windows firewall on SQL Server machine.

Babak Izadi
LotraSoft Ltd.

|||

By default, SQL Server 2005 does not allows remote connections. To configure SQL Server 2005 to allow remote connections, complete all the following steps:

1.Select the SQL Server Surface Area Configuration tool from the Configuration Tools and then click Surface Area Configuration for Services and Connections option. expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply and Ok for restarting the Database engine service.

2. Try adding sqlservr.exe (present in MSSQL.1\MSSQL\Binn directory for the default instance and \MSSQL$instance_name\Binn directory for the named instance) in the list of allowed firewall exceptions on the Windows 2003 box.

Regards,

Rajesh

|||

Can the two servers ping each other?

from serverA run in a command prompt: "ping serverB"

from serverB run in a command prompt: "ping serverA"

If one of the two fails, you probably have a network problem

Else look at the above post's (Rajesh) instructions

|||Can you check whether there are any issues with WINS issue on the network, ifyou are not able to register with name as explained you might try PING between the boxes and also check the firewall exception in this case to avoid any other barrier.

Connectiing to SQL 2005 from ASP Pages

I am having difficulty connecting to SQL 2005 Express from ASP Pages.
My Web site is a mix of data driven ASP.NET 2.0 pages and some complicated
ASP pages which I have not had time to migrate. I recently copied all the my
data from SQL 2000 Server to SQL Express. My ASP.NET pages connect correctly
but when I change the connecting string in my ASP pages the pages break
without returning an error.
My working connecting string in web.config:
Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
Security=True
My working connecting string on my ASP pages:
Provider=SQLOLEDB.1;Data Source=Server2000Name;Initial
Catalog=DatabaseName;User ID=spdbuser;Password=password
Connecting strings I have tried on my ASP pages:
Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Initial
Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
Provider=SQLOLEDB.1;Data Source=.\SQLEXPRESS;Initial
Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
Security=True
I have also tried Data Source=Server2005Name\SQLEXPRESS in the three
connecting string above.
I have verified that a connection is being made from IIS in SQL Express,
using the IUSER account but the page does not serve. I have included IUSER
in the MSSQLUser group and assigned login permissions for the MSSQLUser
group to the database.
Any ideas?
Regards,
Fred Chateau
fchateauAtComcastDotNet
Hi Fred,
Check out these connection strings (they are same for express version):
http://www.connectionstrings.com/
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"Fred Chateau" <fchateau@.127.0.0.1> wrote in message
news:Oi%23IL0B7GHA.4304@.TK2MSFTNGP03.phx.gbl...
>I am having difficulty connecting to SQL 2005 Express from ASP Pages.
> My Web site is a mix of data driven ASP.NET 2.0 pages and some complicated
> ASP pages which I have not had time to migrate. I recently copied all the
> my data from SQL 2000 Server to SQL Express. My ASP.NET pages connect
> correctly but when I change the connecting string in my ASP pages the
> pages break without returning an error.
> My working connecting string in web.config:
> Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
> Security=True
> My working connecting string on my ASP pages:
> Provider=SQLOLEDB.1;Data Source=Server2000Name;Initial
> Catalog=DatabaseName;User ID=spdbuser;Password=password
> Connecting strings I have tried on my ASP pages:
> Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Initial
> Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
> Provider=SQLOLEDB.1;Data Source=.\SQLEXPRESS;Initial
> Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
> Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
> Security=True
> I have also tried Data Source=Server2005Name\SQLEXPRESS in the three
> connecting string above.
> I have verified that a connection is being made from IIS in SQL Express,
> using the IUSER account but the page does not serve. I have included IUSER
> in the MSSQLUser group and assigned login permissions for the MSSQLUser
> group to the database.
> Any ideas?
> Regards,
> Fred Chateau
> fchateauAtComcastDotNet
>
>
|||"Miha Markic [MVP C#]" <miha at rthand com> wrote...

> Hi Fred,

> Check out these connection strings (they are same for express version):
> http://www.connectionstrings.com/
It worked...
I just wanted to mention I got my original connection strings using UDL
(Windows Universal Data Link extension). I've always relied on UDL in the
past but in this scenario UDL definitely doesn't work.
Regards,
Fred Chateau
|||Probably has nothing to do with your connection strings.
1. Make sure you have an acceptable protocol to connect. Open the Config
Manager and add TCP/IP or named pipes (Express is in memory only).
2. If Express is on a different machine than the web server, turn on SQL
Browser
3. Make sure Express is set up for mixed mode. By default, it is not. I do
not have an URL, but google and you should find the command line to switch
to mixed from windows auth.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
*************************************************
Think outside of the box!
*************************************************
"Fred Chateau" <fchateau@.127.0.0.1> wrote in message
news:Oi%23IL0B7GHA.4304@.TK2MSFTNGP03.phx.gbl...
>I am having difficulty connecting to SQL 2005 Express from ASP Pages.
> My Web site is a mix of data driven ASP.NET 2.0 pages and some complicated
> ASP pages which I have not had time to migrate. I recently copied all the
> my data from SQL 2000 Server to SQL Express. My ASP.NET pages connect
> correctly but when I change the connecting string in my ASP pages the
> pages break without returning an error.
> My working connecting string in web.config:
> Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
> Security=True
> My working connecting string on my ASP pages:
> Provider=SQLOLEDB.1;Data Source=Server2000Name;Initial
> Catalog=DatabaseName;User ID=spdbuser;Password=password
> Connecting strings I have tried on my ASP pages:
> Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Initial
> Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
> Provider=SQLOLEDB.1;Data Source=.\SQLEXPRESS;Initial
> Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
> Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
> Security=True
> I have also tried Data Source=Server2005Name\SQLEXPRESS in the three
> connecting string above.
> I have verified that a connection is being made from IIS in SQL Express,
> using the IUSER account but the page does not serve. I have included IUSER
> in the MSSQLUser group and assigned login permissions for the MSSQLUser
> group to the database.
> Any ideas?
> Regards,
> Fred Chateau
> fchateauAtComcastDotNet
>
>
|||Yep, UDL is not a good option anymore.
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"Fred Chateau" <fchateau@.127.0.0.1> wrote in message
news:%23JOYeTF7GHA.3644@.TK2MSFTNGP03.phx.gbl...
> "Miha Markic [MVP C#]" <miha at rthand com> wrote...
>
> It worked...
> I just wanted to mention I got my original connection strings using UDL
> (Windows Universal Data Link extension). I've always relied on UDL in the
> past but in this scenario UDL definitely doesn't work.
> Regards,
> Fred Chateau
>
|||"Cowboy (Gregory A. Beamer)" wrote ...

> Probably has nothing to do with your connection strings.
It _was_ the connection string, Cowboy.
ASP.NET 2.0 --> Data Source=.\SQLEXPRESS;Integrated Security=True
ASP --> Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Integrated
Security=SSPI
"Integrated Security=True" does not work in ASP.
Regards,
Fred Chateau
fchateauAtComcastDotNet

Connectiing to SQL 2005 from ASP Pages

I am having difficulty connecting to SQL 2005 Express from ASP Pages.
My Web site is a mix of data driven ASP.NET 2.0 pages and some complicated
ASP pages which I have not had time to migrate. I recently copied all the my
data from SQL 2000 Server to SQL Express. My ASP.NET pages connect correctly
but when I change the connecting string in my ASP pages the pages break
without returning an error.
My working connecting string in web.config:
Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
Security=True
My working connecting string on my ASP pages:
Provider=SQLOLEDB.1;Data Source=Server2000Name;Initial
Catalog=DatabaseName;User ID=spdbuser;Password=password
Connecting strings I have tried on my ASP pages:
Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Initial
Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
Provider=SQLOLEDB.1;Data Source=.\SQLEXPRESS;Initial
Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
Security=True
I have also tried Data Source=Server2005Name\SQLEXPRESS in the three
connecting string above.
I have verified that a connection is being made from IIS in SQL Express,
using the IUSER account but the page does not serve. I have included IUSER
in the MSSQLUser group and assigned login permissions for the MSSQLUser
group to the database.
Any ideas?
Regards,
Fred Chateau
fchateauAtComcastDotNetHi Fred,
Check out these connection strings (they are same for express version):
http://www.connectionstrings.com/
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"Fred Chateau" <fchateau@.127.0.0.1> wrote in message
news:Oi%23IL0B7GHA.4304@.TK2MSFTNGP03.phx.gbl...
>I am having difficulty connecting to SQL 2005 Express from ASP Pages.
> My Web site is a mix of data driven ASP.NET 2.0 pages and some complicated
> ASP pages which I have not had time to migrate. I recently copied all the
> my data from SQL 2000 Server to SQL Express. My ASP.NET pages connect
> correctly but when I change the connecting string in my ASP pages the
> pages break without returning an error.
> My working connecting string in web.config:
> Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
> Security=True
> My working connecting string on my ASP pages:
> Provider=SQLOLEDB.1;Data Source=Server2000Name;Initial
> Catalog=DatabaseName;User ID=spdbuser;Password=password
> Connecting strings I have tried on my ASP pages:
> Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Initial
> Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
> Provider=SQLOLEDB.1;Data Source=.\SQLEXPRESS;Initial
> Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
> Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
> Security=True
> I have also tried Data Source=Server2005Name\SQLEXPRESS in the three
> connecting string above.
> I have verified that a connection is being made from IIS in SQL Express,
> using the IUSER account but the page does not serve. I have included IUSER
> in the MSSQLUser group and assigned login permissions for the MSSQLUser
> group to the database.
> Any ideas?
> Regards,
> Fred Chateau
> fchateauAtComcastDotNet
>
>|||"Miha Markic [MVP C#]" <miha at rthand com> wrote...

> Hi Fred,

> Check out these connection strings (they are same for express version):
> http://www.connectionstrings.com/
It worked...
I just wanted to mention I got my original connection strings using UDL
(Windows Universal Data Link extension). I've always relied on UDL in the
past but in this scenario UDL definitely doesn't work.
Regards,
Fred Chateau|||Probably has nothing to do with your connection strings.
1. Make sure you have an acceptable protocol to connect. Open the Config
Manager and add TCP/IP or named pipes (Express is in memory only).
2. If Express is on a different machine than the web server, turn on SQL
Browser
3. Make sure Express is set up for mixed mode. By default, it is not. I do
not have an URL, but google and you should find the command line to switch
to mixed from windows auth.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
****************************************
*********
Think outside of the box!
****************************************
*********
"Fred Chateau" <fchateau@.127.0.0.1> wrote in message
news:Oi%23IL0B7GHA.4304@.TK2MSFTNGP03.phx.gbl...
>I am having difficulty connecting to SQL 2005 Express from ASP Pages.
> My Web site is a mix of data driven ASP.NET 2.0 pages and some complicated
> ASP pages which I have not had time to migrate. I recently copied all the
> my data from SQL 2000 Server to SQL Express. My ASP.NET pages connect
> correctly but when I change the connecting string in my ASP pages the
> pages break without returning an error.
> My working connecting string in web.config:
> Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
> Security=True
> My working connecting string on my ASP pages:
> Provider=SQLOLEDB.1;Data Source=Server2000Name;Initial
> Catalog=DatabaseName;User ID=spdbuser;Password=password
> Connecting strings I have tried on my ASP pages:
> Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Initial
> Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
> Provider=SQLOLEDB.1;Data Source=.\SQLEXPRESS;Initial
> Catalog=DatabaseName;Integrated Security=SSPI;Persist Security Info=False
> Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated
> Security=True
> I have also tried Data Source=Server2005Name\SQLEXPRESS in the three
> connecting string above.
> I have verified that a connection is being made from IIS in SQL Express,
> using the IUSER account but the page does not serve. I have included IUSER
> in the MSSQLUser group and assigned login permissions for the MSSQLUser
> group to the database.
> Any ideas?
> Regards,
> Fred Chateau
> fchateauAtComcastDotNet
>
>|||Yep, UDL is not a good option anymore.
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
"Fred Chateau" <fchateau@.127.0.0.1> wrote in message
news:%23JOYeTF7GHA.3644@.TK2MSFTNGP03.phx.gbl...
> "Miha Markic [MVP C#]" <miha at rthand com> wrote...
>
>
> It worked...
> I just wanted to mention I got my original connection strings using UDL
> (Windows Universal Data Link extension). I've always relied on UDL in the
> past but in this scenario UDL definitely doesn't work.
> Regards,
> Fred Chateau
>|||"Cowboy (Gregory A. Beamer)" wrote ...

> Probably has nothing to do with your connection strings.
It _was_ the connection string, Cowboy.
ASP.NET 2.0 --> Data Source=.\SQLEXPRESS;Integrated Security=True
ASP --> Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Integrated
Security=SSPI
"Integrated Security=True" does not work in ASP.
--
Regards,
Fred Chateau
fchateauAtComcastDotNet