Error remotely connecting to SQL Server Express

I've been trying to configure remote connections to my sql server express to no avail.

First here are some constants to clear some things up:
- OS: Windows XP Pro
- SQL Express version: April CTP release
- Windows Firewall: disabled (to avoid any problems there)
- Network type: two computers connected a router.
- Router firewall: Enabled (I may be wrong but the router firewall only blocks external traffic, so I left it on.)

First, I configured my SQL server to allow remote connections in the SQL Surface Area Configuration. (Set it to both TCP/IP and named pipes)

Second, I enabled and configured sql server browser.

Third, I enabled TCP/IP and named pipes in the 'Protocols for SQL Express' option of the SQL Server Configuration Manager.

According to http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
I have done everything I need to (including edit the registry to enable the SQL Server Browser service) and the error I continue to get is:

"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, Error: -1)"

So I tinkered and had no success in messing with anything that would fix it. Although, ***when I browsed for network servers on different machine, the server instance DID show up in the list***. So I find it odd that it can find the instance when browsing, but fails to 'find' it when making a connection.

So I didn't stop there, I decided to try the non-sql server browser method of remotely connecting: specifying the IP.

I went back to the blog of instructions referenced above and re-did the steps, this time specifying a TCP Port for the server instance and then after specifying the port on the remote computer connection string, and attempting to connect, I get this 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: 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.) (Microsoft SQL Server, Error: 10060)"

I've done all I know how, read all the possible blogs I can, and nothing's come up. If anyone can shed some light on me, I'd pay you 1 billion pretend dollars. Any help would be appreciated. Thank you!

First of all, why are you using a CTP version of SQL Server Express ?

What is your connection string / with which application are you trying to connect ?

Is SQL Server Browser started on the server ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Error processing a report connecting to an external SQL db

I created a report that uses data from an external SQL Server database. I
specified Windows Authentication. My NT account has full rights on the
external SQL server. When I run a report with ''localhost'' in the URL, it
works fine. However, when I use the actual server name, it first prompts me
for NT login/password (which is fine), but then I get the following:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection do data source 'MyDataSource'.
(rsErrorOpeningConnection)
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
My assumption after reading MS docs was that the Reporting Server would use
the current user's credentials to connect to the external data source, but
it's not happening.
Any ideas on how to fix this would be highly appreciated.
Thanks,
LeoHi Leo,
Did you find a solution to this? I have the same problem. Are running
Reporting Services/SQL Server 2005 and can not run reports which get data
from an external SQL Server 2000.
Thanks,
Soren
"LeoW" wrote:
> I created a report that uses data from an external SQL Server database. I
> specified Windows Authentication. My NT account has full rights on the
> external SQL server. When I run a report with ''localhost'' in the URL, it
> works fine. However, when I use the actual server name, it first prompts me
> for NT login/password (which is fine), but then I get the following:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection do data source 'MyDataSource'.
> (rsErrorOpeningConnection)
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> My assumption after reading MS docs was that the Reporting Server would use
> the current user's credentials to connect to the external data source, but
> it's not happening.
> Any ideas on how to fix this would be highly appreciated.
> Thanks,
> Leo|||Any answer on this? I am having this problem as well. I am still just
running 2000 though. I am able to open and connect with several other
reports. But I have one that is not connecting and getting this error when I
click on the data tab. When I create the data source and test the
connection, it says it connects successfully.
Please someone answer this questions!!!!!!!!!!!!! :)
"LeoW" wrote:
> I created a report that uses data from an external SQL Server database. I
> specified Windows Authentication. My NT account has full rights on the
> external SQL server. When I run a report with ''localhost'' in the URL, it
> works fine. However, when I use the actual server name, it first prompts me
> for NT login/password (which is fine), but then I get the following:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection do data source 'MyDataSource'.
> (rsErrorOpeningConnection)
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> My assumption after reading MS docs was that the Reporting Server would use
> the current user's credentials to connect to the external data source, but
> it's not happening.
> Any ideas on how to fix this would be highly appreciated.
> Thanks,
> Leo|||+1. I am hitting the same issue as well. Does any one have a solution for this?
Thanks
Sushmitha
"SharinDenver" wrote:
> Any answer on this? I am having this problem as well. I am still just
> running 2000 though. I am able to open and connect with several other
> reports. But I have one that is not connecting and getting this error when I
> click on the data tab. When I create the data source and test the
> connection, it says it connects successfully.
> Please someone answer this questions!!!!!!!!!!!!! :)
> "LeoW" wrote:
> > I created a report that uses data from an external SQL Server database. I
> > specified Windows Authentication. My NT account has full rights on the
> > external SQL server. When I run a report with ''localhost'' in the URL, it
> > works fine. However, when I use the actual server name, it first prompts me
> > for NT login/password (which is fine), but then I get the following:
> > An error has occurred during report processing. (rsProcessingAborted)
> > Cannot create a connection do data source 'MyDataSource'.
> > (rsErrorOpeningConnection)
> > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > Server connection.
> >
> > My assumption after reading MS docs was that the Reporting Server would use
> > the current user's credentials to connect to the external data source, but
> > it's not happening.
> >
> > Any ideas on how to fix this would be highly appreciated.
> >
> > Thanks,
> > Leo|||Was this ever resolved? I see that the last post was posted a few months ago.
I am looking for a soltuion to the following:
Server A is the Web, SQL, and RS Server (2k all around). No issues with
reports.
Server B is a 2nd SQL box (2k as well.)
I create a report in VS 2k03 and can preview it no problem using a shared
data source with stored credentials.
When i deploy the report and try to generate it via Report Mgr, I get
"Cannot create a connection to data source..."
Any hints?
Much appreciated.
"Sushmitha Kini" wrote:
> +1. I am hitting the same issue as well. Does any one have a solution for this?
> Thanks
> Sushmitha
> "SharinDenver" wrote:
> > Any answer on this? I am having this problem as well. I am still just
> > running 2000 though. I am able to open and connect with several other
> > reports. But I have one that is not connecting and getting this error when I
> > click on the data tab. When I create the data source and test the
> > connection, it says it connects successfully.
> >
> > Please someone answer this questions!!!!!!!!!!!!! :)
> >
> > "LeoW" wrote:
> >
> > > I created a report that uses data from an external SQL Server database. I
> > > specified Windows Authentication. My NT account has full rights on the
> > > external SQL server. When I run a report with ''localhost'' in the URL, it
> > > works fine. However, when I use the actual server name, it first prompts me
> > > for NT login/password (which is fine), but then I get the following:
> > > An error has occurred during report processing. (rsProcessingAborted)
> > > Cannot create a connection do data source 'MyDataSource'.
> > > (rsErrorOpeningConnection)
> > > Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> > > Server connection.
> > >
> > > My assumption after reading MS docs was that the Reporting Server would use
> > > the current user's credentials to connect to the external data source, but
> > > it's not happening.
> > >
> > > Any ideas on how to fix this would be highly appreciated.
> > >
> > > Thanks,
> > > Leo

Error Message When Connecting to SQl Server Data

I am using MSDE on XP pro Service Pack 2 and have stored my data on the
default instance of MSSQLSERVER on my computer ID OFFICEPC. This is
networked to LAPTOP and SHOPPC.
My Access front end works fine with the SQL back end on the OFFICEPC. But
when I try to link the same front end from one of the other computers using
ODBC, i get the following error:
Connection Failed:
SQL state 2800
SQL Server Error 18456
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
'OFFICEPC\Guest'
I have run svrnetcn.exe and enabled port 1433 to pass through the firewall.
Ports 135 and 445 are also enabled.
What have I done wrong? Thanks in advance for any help
hi Gerald,
"Gerald G" <GeraldG@.discussions.microsoft.com> ha scritto nel messaggio
news:54F59F97-F083-4C93-B08A-4FCEA3429731@.microsoft.com
> I am using MSDE on XP pro Service Pack 2 and have stored my data on
> the default instance of MSSQLSERVER on my computer ID OFFICEPC. This
> is networked to LAPTOP and SHOPPC.
> My Access front end works fine with the SQL back end on the OFFICEPC.
> But when I try to link the same front end from one of the other
> computers using ODBC, i get the following error:
> Connection Failed:
> SQL state 2800
> SQL Server Error 18456
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
> 'OFFICEPC\Guest'
> I have run svrnetcn.exe and enabled port 1433 to pass through the
> firewall. Ports 135 and 445 are also enabled.
> What have I done wrong? Thanks in advance for any help
it seems you do not have firewall troubles, but standard security problems,
as OFFICEPC\.. users are not granted login to MSDE instance... please verify
your Logins
can you telnet on port 1433 the MSDE instance?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea - thanks for answering
I think you are right about it being a standard security problem. How do I
telnet on port 1433, and How do I grant login to my MSDE instance?
"Andrea Montanari" wrote:

> hi Gerald,
> "Gerald G" <GeraldG@.discussions.microsoft.com> ha scritto nel messaggio
> news:54F59F97-F083-4C93-B08A-4FCEA3429731@.microsoft.com
> it seems you do not have firewall troubles, but standard security problems,
> as OFFICEPC\.. users are not granted login to MSDE instance... please verify
> your Logins
> can you telnet on port 1433 the MSDE instance?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Gerald
"Gerald G" <GeraldG@.discussions.microsoft.com> ha scritto nel messaggio
news:7CD6EC0F-53A4-4252-9228-A40089BE66FF@.microsoft.com
> Hi Andrea - thanks for answering
> I think you are right about it being a standard security problem.
> How do I telnet on port 1433, and How do I grant login to my MSDE
> instance?
>
in order to grant your 'OFFICEPC\xxxx' users, you have to add them to the
SQL Server Windows logins...
please have a look at
http://support.microsoft.com/default...N-US;q325003#8 for
further info about granting login to Windows logins/groups
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Error Message 11004

Hi
Eveyone. I am having trouble connecting to my SQL Server 2005
Expression edition. I can connnect from other pc without any trouble.
The only difference between this PC and the others is this PC is
running Windows 2000 SP4. I can ping my SQL Server no problem. I can
even telnet into it. I just can't connect to it. Any help would be
greatly appreciated.
-Thanks
Thanascheck sql log to see which tcp port sqlexpress started under. then try to
connect via:
sqlcmd -S"servername,tcp_port"
-oj
<atsioplakis@.gmail.com> wrote in message
news:1150481091.896901.173220@.c74g2000cwc.googlegroups.com...
> Hi
> Eveyone. I am having trouble connecting to my SQL Server 2005
> Expression edition. I can connnect from other pc without any trouble.
> The only difference between this PC and the others is this PC is
> running Windows 2000 SP4. I can ping my SQL Server no problem. I can
> even telnet into it. I just can't connect to it. Any help would be
> greatly appreciated.
> -Thanks
> Thanas
>|||check sql log to see which tcp port sqlexpress started under. then try to
connect via:
sqlcmd -S"servername,tcp_port"
-oj
<atsioplakis@.gmail.com> wrote in message
news:1150481091.896901.173220@.c74g2000cwc.googlegroups.com...
> Hi
> Eveyone. I am having trouble connecting to my SQL Server 2005
> Expression edition. I can connnect from other pc without any trouble.
> The only difference between this PC and the others is this PC is
> running Windows 2000 SP4. I can ping my SQL Server no problem. I can
> even telnet into it. I just can't connect to it. Any help would be
> greatly appreciated.
> -Thanks
> Thanas
>|||The trouble is that Windows 2000 OS Data Source section doesn't want to
connect to the SQL Server. I can't get that to work. Any comments on
that?
oj wrote:[vbcol=seagreen]
> check sql log to see which tcp port sqlexpress started under. then try to
> connect via:
> sqlcmd -S"servername,tcp_port"
> --
> -oj
>
> <atsioplakis@.gmail.com> wrote in message
> news:1150481091.896901.173220@.c74g2000cwc.googlegroups.com...|||The trouble is that Windows 2000 OS Data Source section doesn't want to
connect to the SQL Server. I can't get that to work. Any comments on
that?
oj wrote:[vbcol=seagreen]
> check sql log to see which tcp port sqlexpress started under. then try to
> connect via:
> sqlcmd -S"servername,tcp_port"
> --
> -oj
>
> <atsioplakis@.gmail.com> wrote in message
> news:1150481091.896901.173220@.c74g2000cwc.googlegroups.com...|||did you try to connect with explicitly specifying the tcp port#.
to determine the listening port, connect to the instance and run "exec
sp_readerrorlog". this displays the sql error log and you should be able to
find the current listening port.
also, check to see if there is a firewall between the win2k and sql2k5
express. make sure sqlbrowser is running on the sqlserver and UDP 1434 is
open.
-oj
"Thanas" <atsioplakis@.gmail.com> wrote in message
news:1150741206.158850.127400@.p79g2000cwp.googlegroups.com...
> The trouble is that Windows 2000 OS Data Source section doesn't want to
> connect to the SQL Server. I can't get that to work. Any comments on
> that?
> oj wrote:
>|||did you try to connect with explicitly specifying the tcp port#.
to determine the listening port, connect to the instance and run "exec
sp_readerrorlog". this displays the sql error log and you should be able to
find the current listening port.
also, check to see if there is a firewall between the win2k and sql2k5
express. make sure sqlbrowser is running on the sqlserver and UDP 1434 is
open.
-oj
"Thanas" <atsioplakis@.gmail.com> wrote in message
news:1150741206.158850.127400@.p79g2000cwp.googlegroups.com...
> The trouble is that Windows 2000 OS Data Source section doesn't want to
> connect to the SQL Server. I can't get that to work. Any comments on
> that?
> oj wrote:
>

Error Message 11004

Hi
Eveyone. I am having trouble connecting to my SQL Server 2005
Expression edition. I can connnect from other pc without any trouble.
The only difference between this PC and the others is this PC is
running Windows 2000 SP4. I can ping my SQL Server no problem. I can
even telnet into it. I just can't connect to it. Any help would be
greatly appreciated.
-Thanks
Thanascheck sql log to see which tcp port sqlexpress started under. then try to
connect via:
sqlcmd -S"servername,tcp_port"
--
-oj
<atsioplakis@.gmail.com> wrote in message
news:1150481091.896901.173220@.c74g2000cwc.googlegroups.com...
> Hi
> Eveyone. I am having trouble connecting to my SQL Server 2005
> Expression edition. I can connnect from other pc without any trouble.
> The only difference between this PC and the others is this PC is
> running Windows 2000 SP4. I can ping my SQL Server no problem. I can
> even telnet into it. I just can't connect to it. Any help would be
> greatly appreciated.
> -Thanks
> Thanas
>|||The trouble is that Windows 2000 OS Data Source section doesn't want to
connect to the SQL Server. I can't get that to work. Any comments on
that?
oj wrote:
> check sql log to see which tcp port sqlexpress started under. then try to
> connect via:
> sqlcmd -S"servername,tcp_port"
> --
> -oj
>
> <atsioplakis@.gmail.com> wrote in message
> news:1150481091.896901.173220@.c74g2000cwc.googlegroups.com...
> > Hi
> > Eveyone. I am having trouble connecting to my SQL Server 2005
> > Expression edition. I can connnect from other pc without any trouble.
> > The only difference between this PC and the others is this PC is
> > running Windows 2000 SP4. I can ping my SQL Server no problem. I can
> > even telnet into it. I just can't connect to it. Any help would be
> > greatly appreciated.
> >
> > -Thanks
> > Thanas
> >|||did you try to connect with explicitly specifying the tcp port#.
to determine the listening port, connect to the instance and run "exec
sp_readerrorlog". this displays the sql error log and you should be able to
find the current listening port.
also, check to see if there is a firewall between the win2k and sql2k5
express. make sure sqlbrowser is running on the sqlserver and UDP 1434 is
open.
-oj
"Thanas" <atsioplakis@.gmail.com> wrote in message
news:1150741206.158850.127400@.p79g2000cwp.googlegroups.com...
> The trouble is that Windows 2000 OS Data Source section doesn't want to
> connect to the SQL Server. I can't get that to work. Any comments on
> that?
> oj wrote:
>> check sql log to see which tcp port sqlexpress started under. then try to
>> connect via:
>> sqlcmd -S"servername,tcp_port"
>> --
>> -oj
>>
>> <atsioplakis@.gmail.com> wrote in message
>> news:1150481091.896901.173220@.c74g2000cwc.googlegroups.com...
>> > Hi
>> > Eveyone. I am having trouble connecting to my SQL Server 2005
>> > Expression edition. I can connnect from other pc without any trouble.
>> > The only difference between this PC and the others is this PC is
>> > running Windows 2000 SP4. I can ping my SQL Server no problem. I can
>> > even telnet into it. I just can't connect to it. Any help would be
>> > greatly appreciated.
>> >
>> > -Thanks
>> > Thanas
>> >
>