Friday, March 9, 2012

Error connecting to SQL Server 2005 Express from Access

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 t
o
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\SQLEXPRESS" _
& ";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 m
ade
because the target machine actively refused it.
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]An error has occurred while establishi
ng 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 no
t
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,
ChrisBy 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@.discussions.microsoft.com> wrote in message
news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.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\SQLEXPRESS" _
> & ";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 establis
hing 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, 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 connectio
n
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 Name
d
> 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@.discussions.microsoft.com> wrote in messag
e
> news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>
>|||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@.discussions.microsoft.com> wrote in message
news:BD09DFBD-66E6-411D-AA16-2A89D9672C1B@.microsoft.com...[vbcol=seagreen]
> 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:
>

No comments:

Post a Comment