MS SQL Server ODBC - Error connecting to SQL Server 2005 Express from Access

Author

Error connecting to SQL Server 2005 Express from Access

Chris Burnette

2005-11-11, 1:23 pm

I am trying to connect to SQL Server 2005 Express from Access using a
DSN-less connection string, but I keep encountering errors. What happens is
that I have a login form with 2 text boxes for users to enter their username
and password. When they have entered them, they click OK and Access tries to
connect using the connection string below.

After waiting a while I get an error, however the SQL Server Login box
appears and I am able to login using my sa login. So, since I know I can
connect to SQL Server, I think the problem must be with my connection string:

stConnect = "ODBC;DRIVER={SQL Native Client}" _
& " ;Trusted_Connection=
no" _
& " ;SERVER=CHRIS\SQLEXP
RESS" _
& ";Address=10.0.0.17,1433" _
& ";Network=DBMSSOCN" _
& ";DATABASE=authorDB 2005_11_11SQL" _
& ";UID=" & stUID _
& ";PWD=" & stPWD & ";"

These are the error messages I get when I try to connect:

Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]TCP Provider: No connection could be made
because the target machine actively refused it.

Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]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.
Connection failed:
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired

I should mention that I was able to use a similar connection string to
connect to an MSDE instance with no problems, so if need be I can just use
MSDE. Still, I would prefer to use SQL Server Express since it doesn't have
a workload governor. Also, this is on my local machine, so there shouldn't
be any network issues.

I would appreciate any assistance that anyone can offer.

Thanks,

Chris

Gert E.R. Drapers

2005-11-14, 3:23 am

By default SQL Express does not accept remote connections over TCP or Named
Pipes, you need to enable that explicitly.

GertD@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.

"Chris Burnette" < ChrisBurnette@discus
sions.microsoft.com> wrote in message
news:4FAF0093-8C11-4030-8E4C- 8621DE179732@microso
ft.com...
>I am trying to connect to SQL Server 2005 Express from Access using a
> DSN-less connection string, but I keep encountering errors. What happens
> is
> that I have a login form with 2 text boxes for users to enter their
> username
> and password. When they have entered them, they click OK and Access tries
> to
> connect using the connection string below.
>
> After waiting a while I get an error, however the SQL Server Login box
> appears and I am able to login using my sa login. So, since I know I can
> connect to SQL Server, I think the problem must be with my connection
> string:
>
> stConnect = "ODBC;DRIVER={SQL Native Client}" _
> & " ;Trusted_Connection=
no" _
> & " ;SERVER=CHRIS\SQLEXP
RESS" _
> & ";Address=10.0.0.17,1433" _
> & ";Network=DBMSSOCN" _
> & ";DATABASE=authorDB 2005_11_11SQL" _
> & ";UID=" & stUID _
> & ";PWD=" & stPWD & ";"
>
> These are the error messages I get when I try to connect:
>
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]TCP Provider: No connection could be made
> because the target machine actively refused it.
>
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]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.
> Connection failed:
> SQLState: 'S1T00'
> SQL Server Error: 0
> [Microsoft][SQL Native Client]Login timeout expired
>
>
> I should mention that I was able to use a similar connection string to
> connect to an MSDE instance with no problems, so if need be I can just use
> MSDE. Still, I would prefer to use SQL Server Express since it doesn't
> have
> a workload governor. Also, this is on my local machine, so there
> shouldn't
> be any network issues.
>
> I would appreciate any assistance that anyone can offer.
>
> Thanks,
>
> Chris

Chris Burnette

2005-11-14, 9:23 am

Gert, I know that. I started the Browser and enabled TCP/IP. Like I said, I
can connect using the sa login, and techinically it's not a remote connection
as it's on a local machine. Thanks for the response, but I already checked
and that isn't the problem.

-Chris

"Gert E.R. Drapers" wrote:

> By default SQL Express does not accept remote connections over TCP or Named
> Pipes, you need to enable that explicitly.
>
> GertD@SQLDev.Net
>
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright Â© SQLDev.Net 1991-2005 All rights reserved.
>
> "Chris Burnette" < ChrisBurnette@discus
sions.microsoft.com> wrote in message
> news:4FAF0093-8C11-4030-8E4C- 8621DE179732@microso
ft.com...
>
>
>

Chris Burnette

2005-11-15, 1:23 pm

I figured it out. I just needed to change the default port for TCP/IP since
MSDE was using port 1433.

-Chris

"Chris Burnette" < ChrisBurnette@discus
sions.microsoft.com> wrote in message
news:BD09DFBD-66E6-411D-AA16- 2A89D9672C1B@microso
ft.com...[color=darkred]
> Gert, I know that. I started the Browser and enabled TCP/IP. Like I
> said, I
> can connect using the sa login, and techinically it's not a remote
> connection
> as it's on a local machine. Thanks for the response, but I already
> checked
> and that isn't the problem.
>
> -Chris
>
> "Gert E.R. Drapers" wrote:
>

Sponsored Links