Can't connect to local SQL Server 2005 from .adp

  • Thread starter Thread starter Francis Moore
  • Start date Start date
F

Francis Moore

First off, I'm using Access 2007 SP1, Vista SP1 and SQL Server 2005 Developer
Edition SP2 (Build 3068), all on my local computer.

It seems that I can connect to my server from any application but from an
Access adp... I can't figure out why... It's the first time I'm using an adp,
when I try to connect to SQL Server I get the error:

Test connection failed because of an error in initializing provider.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.

I'm able to connect to SQL Server from SSMS, from Visual Studio 2008 and I
can create an ODBC datasource pointing to a database on the server and test
succesfully the connection.

I tried both with Integrated Security and username/password (which is
enabled on the server) without luck. I tried to disable the windows firewall,
it didn't help either.

Any ideas?
 
Check that you're ADP connection is using a communication method that is
enabled on your SQL Server. I think the default SQL Server install might
only enable shared memory connections.
 
Thank you for your reply, but where can I find these settings?

Paul Shapiro said:
Check that you're ADP connection is using a communication method that is
enabled on your SQL Server. I think the default SQL Server install might
only enable shared memory connections.

Francis Moore said:
First off, I'm using Access 2007 SP1, Vista SP1 and SQL Server 2005
Developer
Edition SP2 (Build 3068), all on my local computer.

It seems that I can connect to my server from any application but from an
Access adp... I can't figure out why... It's the first time I'm using an
adp,
when I try to connect to SQL Server I get the error:

Test connection failed because of an error in initializing provider.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.

I'm able to connect to SQL Server from SSMS, from Visual Studio 2008 and I
can create an ODBC datasource pointing to a database on the server and
test
succesfully the connection.

I tried both with Integrated Security and username/password (which is
enabled on the server) without luck. I tried to disable the windows
firewall,
it didn't help either.
Click to expand...
Click to expand...
 
The first thing to do would be to try adding the prefix np: or tcp: (for
either Named Pipes or TCP/IP) before the name of the server. For tcp/ip,
you can also try adding the port number (,1433) after the name of the
server. If this is not the default instance, you must add the name of the
instance after the name of the server; for example:

sqlcmd -S tcp:NameOfTheServer\sqlexpress,1433

You can also try other names for the server address, like:

..
(local)
localhost
127.0.0.1

Finally, check if you would have created any alias for the sql-server on the
machines which can connect properly. If you are using an alias then you
should recreate this alias on your new Vista machine. Here are some
articles to help you:

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

http://www.datamasker.com/SSE2005_NetworkCfg.htm

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Francis Moore said:
Thank you for your reply, but where can I find these settings?

Paul Shapiro said:
Check that you're ADP connection is using a communication method that is
enabled on your SQL Server. I think the default SQL Server install might
only enable shared memory connections.

Francis Moore said:
First off, I'm using Access 2007 SP1, Vista SP1 and SQL Server 2005
Developer
Edition SP2 (Build 3068), all on my local computer.

It seems that I can connect to my server from any application but from
an
Access adp... I can't figure out why... It's the first time I'm using
an
adp,
when I try to connect to SQL Server I get the error:

Test connection failed because of an error in initializing provider.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.

I'm able to connect to SQL Server from SSMS, from Visual Studio 2008
and I
can create an ODBC datasource pointing to a database on the server and
test
succesfully the connection.

I tried both with Integrated Security and username/password (which is
enabled on the server) without luck. I tried to disable the windows
firewall,
it didn't help either.
Click to expand...
Click to expand...
Click to expand...
 
I accidentaly found the solution while trying suggestions from the first
article! I had to enable remote connections in Surface Area Configuration for
Services and Connection. Under Database Engine/Remote Connections I choose
"Local and remote connection" / "Using TCP/IP only".

I learned that remote connections are not allowed by default for the
Developer Edition.

I don't know why Access needs that remote connections be allowed, but anyway
my problem is solved!

Thanks Paul and Sylvain for your help!

Sylvain Lafontaine said:
The first thing to do would be to try adding the prefix np: or tcp: (for
either Named Pipes or TCP/IP) before the name of the server. For tcp/ip,
you can also try adding the port number (,1433) after the name of the
server. If this is not the default instance, you must add the name of the
instance after the name of the server; for example:

sqlcmd -S tcp:NameOfTheServer\sqlexpress,1433

You can also try other names for the server address, like:

..
(local)
localhost
127.0.0.1

Finally, check if you would have created any alias for the sql-server on the
machines which can connect properly. If you are using an alias then you
should recreate this alias on your new Vista machine. Here are some
articles to help you:

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

http://www.datamasker.com/SSE2005_NetworkCfg.htm

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Francis Moore said:
Thank you for your reply, but where can I find these settings?

Paul Shapiro said:
Check that you're ADP connection is using a communication method that is
enabled on your SQL Server. I think the default SQL Server install might
only enable shared memory connections.

First off, I'm using Access 2007 SP1, Vista SP1 and SQL Server 2005
Developer
Edition SP2 (Build 3068), all on my local computer.

It seems that I can connect to my server from any application but from
an
Access adp... I can't figure out why... It's the first time I'm using
an
adp,
when I try to connect to SQL Server I get the error:

Test connection failed because of an error in initializing provider.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.

I'm able to connect to SQL Server from SSMS, from Visual Studio 2008
and I
can create an ODBC datasource pointing to a database on the server and
test
succesfully the connection.

I tried both with Integrated Security and username/password (which is
enabled on the server) without luck. I tried to disable the windows
firewall,
it didn't help either.
Click to expand...
Click to expand...
Click to expand...
 
Glad you got it working. One caution is that your SQL Server is now
available to any computer which can connect to yours on TCP port 1433.
That's why SQL 2005 does not enable remote connections by default. So make
sure your db is secured and keep SQL Server patched.

I think you can get Access to connect over the shared memory protocol, which
is what SQL 2005 expects you to do in the default configuration. Try using
(local) as the server name, with the parentheses. I'm not sure I remember
that correctly, so if it doesn't work, see what SQL Books OnLine says about
shared memory connections. If you get that working, you can disable the
remote connections again. The shared memory protocol only works if you're
connecting from the same machine where SQL Server is running, so if you're
using more than one computer don't bother trying.

Francis Moore said:
I accidentaly found the solution while trying suggestions from the first
article! I had to enable remote connections in Surface Area Configuration
for
Services and Connection. Under Database Engine/Remote Connections I choose
"Local and remote connection" / "Using TCP/IP only".

I learned that remote connections are not allowed by default for the
Developer Edition.

I don't know why Access needs that remote connections be allowed, but
anyway
my problem is solved!

Thanks Paul and Sylvain for your help!

Sylvain Lafontaine said:
The first thing to do would be to try adding the prefix np: or tcp: (for
either Named Pipes or TCP/IP) before the name of the server. For tcp/ip,
you can also try adding the port number (,1433) after the name of the
server. If this is not the default instance, you must add the name of
the
instance after the name of the server; for example:

sqlcmd -S tcp:NameOfTheServer\sqlexpress,1433

You can also try other names for the server address, like:

..
(local)
localhost
127.0.0.1

Finally, check if you would have created any alias for the sql-server on
the
machines which can connect properly. If you are using an alias then you
should recreate this alias on your new Vista machine. Here are some
articles to help you:

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

http://www.datamasker.com/SSE2005_NetworkCfg.htm

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Francis Moore said:
Thank you for your reply, but where can I find these settings?

:

Check that you're ADP connection is using a communication method that
is
enabled on your SQL Server. I think the default SQL Server install
might
only enable shared memory connections.

message
First off, I'm using Access 2007 SP1, Vista SP1 and SQL Server 2005
Developer
Edition SP2 (Build 3068), all on my local computer.

It seems that I can connect to my server from any application but
from
an
Access adp... I can't figure out why... It's the first time I'm
using
an
adp,
when I try to connect to SQL Server I get the error:

Test connection failed because of an error in initializing provider.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.

I'm able to connect to SQL Server from SSMS, from Visual Studio 2008
and I
can create an ODBC datasource pointing to a database on the server
and
test
succesfully the connection.

I tried both with Integrated Security and username/password (which
is
enabled on the server) without luck. I tried to disable the windows
firewall,
it didn't help either.
Click to expand...
Click to expand...
Click to expand...
 
does shared memory give the best throughput?

so now do typical 'hardcore olap people' put their olap servers on the
same machine as the db server?


Check that you're ADP connection is using a communication method that is
enabled on your SQL Server. I think the default SQL Server install might
only enable shared memory connections.


First off, I'm using Access 2007 SP1, Vista SP1 and SQL Server 2005
Developer
Edition SP2 (Build 3068), all on my local computer.
Click to expand...
It seems that I can connect to my server from any application but from an
Access adp... I can't figure out why... It's the first time I'm using an
adp,
when I try to connect to SQL Server I get the error:
Click to expand...
Test connection failed because of an error in initializing provider.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.
Click to expand...
I'm able to connect to SQL Server from SSMS, from Visual Studio 2008 and I
can create an ODBC datasource pointing to a database on the server and
test
succesfully the connection.
Click to expand...
I tried both with Integrated Security and username/password (which is
enabled on the server) without luck. I tried to disable the windows
firewall,
it didn't help either.
Click to expand...
Click to expand...
 
ÊÕµ½
Paul Shapiro said:
Check that you're ADP connection is using a communication method that is
enabled on your SQL Server. I think the default SQL Server install might
only enable shared memory connections.

Francis Moore said:
First off, I'm using Access 2007 SP1, Vista SP1 and SQL Server 2005
Developer
Edition SP2 (Build 3068), all on my local computer.

It seems that I can connect to my server from any application but from an
Access adp... I can't figure out why... It's the first time I'm using an
adp,
when I try to connect to SQL Server I get the error:

Test connection failed because of an error in initializing provider.
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.

I'm able to connect to SQL Server from SSMS, from Visual Studio 2008 and I
can create an ODBC datasource pointing to a database on the server and
test
succesfully the connection.

I tried both with Integrated Security and username/password (which is
enabled on the server) without luck. I tried to disable the windows
firewall,
it didn't help either.
Click to expand...
Click to expand...
 
Back
Top