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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <FrancisMoore@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:62B29D7C-DDAE-48DE-BEE6-DE917077944A@xxxxxxxxxxxxxxxx
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" wrote:

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/archive/2007/07/19/tcp-provider-no-connection-could-be-made-because-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" <FrancisMoore@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:41258D86-13D9-4E1E-9243-3F6C70708C06@xxxxxxxxxxxxxxxx
> Thank you for your reply, but where can I find these settings?
>
> "Paul Shapiro" wrote:
>
>> 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" <FrancisMoore@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in >> message
>> news:9DFC5586-5232-40DF-9331-D8AE351284B4@xxxxxxxxxxxxxxxx
>> > 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.
>>
>>




.

Relevant Pages

  • Re: VB app connect THROUGH proxy to a remote SQL database
    ... The whole "HTTP proxy is way more secure than allowing outgoing ... the client app and the SQL server are on ... incoming connections to the SQL server are not the problem - it's ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... If I cut the maximum number of connections to for ... SQL Server simply blocks any further try to open another table until I close ... This doesn't happen with the SQL Server 2000 but with SQL Server ... No, I think ms-sql-s means that SQL 2000 is using port 1433, which is ...
    (microsoft.public.sqlserver.connect)
  • Re: Error while connecting to SQL 2005 from ASP.Net 2.0
    ... When connecting to SQL Server ... Server does not allow remote connections. ... ASP.NET webservice to the SQL Server 2005 box. ... If so, as the error message indicate, the SQL ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SQL Management Studio cannot connect remotely
    ... firewall has the TCP port 1433 and UDP port 1434 open). ... Server is listening on TCP port 1433. ... Use SQL Server Configuration ... protocol connections are using. ...
    (microsoft.public.sqlserver.connect)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... Uninstall the SQL server from the SBS 2k3 server from add/remove programs ... Uninstall Microsoft SQL Server Desktop Engine (SHAREPOINT) ... If AV software install any extra IIS virtual directory, ...
    (microsoft.public.windows.server.sbs)