By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,889 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,889 IT Pros & Developers. It's quick & easy.

Connection problem from Access front end application to SQL Desktop Engine backend

P: n/a
Hi there,

I sincerely hope that someone out there can help. I have two instances
of the SQL 2000 Desktop Engine running. One is on my local machine for
development and the other is on another machine on our network which is
the production environment. I have built an Access 2003 front end
application which connects to this database. This works fine locally,
as you would expect. I successfully installed the database on the
production machine and am able to connect to it via Access 2003 (using
the Data Link Properties window) and from third party database manager
software (similar to Enterprise Manager). I am not able to to connect
to the database via my application.

I am using the "sa" account with a strong password. This is my
connection string:

strConnection = "Provider=sqloledb;DataSource=server02;User
Id=sa;Password=strong;Initial Catalog=Test"

The error I'm getting is:

"Connection cannot be used to perform this operation. It is either
closed or invalid in this context."

The connection string is the only thing that changes in my code when I
switch from my local to my production environment. Is there some
reason that I can't use the "sa" account in this fashion that I'm not
aware of? I'd rather not use integrated security for simplicity's sake
as this is a small, internal application. Also, I would have thought
that if that was the issue, I couldn't use "sa" at all, even locally.

I have posted to the SQL Server group already but thought someone here
might have some advice to offer.

Thanks,

Barb

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY
INFO=FALSE;INITIAL CATALOG=TEST;DATA SOURCE=SERVERNAME

Also try signing in with a valid Windows user account that is a member
of a role or group on the server.

Nov 13 '05 #2

P: n/a
PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY
INFO=FALSE;INITIAL CATALOG=TEST;DATA SOURCE=SERVERNAME

Also try signing in with a valid Windows user account that is a member
of a role or group on the server.

Nov 13 '05 #3

P: n/a
Hi there,

Thanks for the advice. Can I assume from your response that it's not
possible to use the "sa" account in this fashion? As I had mentioned,
I'd prefer to not use integrated security if possible. Since I can
access the SQL server with the "sa" account in all instances except
from the application to the server, I wouldn't think that was the
problem but if someone could confirm that's definitely the case, I'd be
grateful.

Barb
lauren quantrell wrote:
PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY
INFO=FALSE;INITIAL CATALOG=TEST;DATA SOURCE=SERVERNAME

Also try signing in with a valid Windows user account that is a member of a role or group on the server.


Nov 13 '05 #4

P: n/a
Barb,

Can you post the code that you're using to make the connection? Show
us the code that works and the code that doesn't.

Bill E.

Nov 13 '05 #5

P: n/a
Hi Bill,

The connection string is exactly the same as the line that I originally
posted with the exception of the data source parameter. Here's the
code for the sub in question as you requested. The development
connection string is commented out here because I'm trying to connect
to the production database. The only change that would happen in my
code if I was working with the dev database is that I'd comment out the
production connection string and comment in the dev connection string.
When trying to connect to the production database I error on
cnConn.Open strConnection. The error I'm actually getting is "Invalid
connection string attribute" (-2147217843).

Hope this is enough information and thanks to everyone for their help
so far.

Barb

Sub OpenDbConnection()
On Error GoTo HandleError

'dev connection string
'strConnection = "Provider=sqloledb;DataSource=local;User
Id=sa;Password=br8111;" & _
"Initial Catalog=SubscriberMngt"

'production connection string
strConnection = "Provider=sqloledb;DataSource=server02;User
Id=sa;Password=qr7959;" & _
"Initial Catalog=SubscriberMngt"

'create new connection instance and open it using the connection
string
Set cnConn = New ADODB.Connection
cnConn.Open strConnection

Exit Sub

HandleError:
GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC, _
"OpenDbConnection"
End Sub
billmia...@netscape.net wrote:
Barb,

Can you post the code that you're using to make the connection? Show
us the code that works and the code that doesn't.

Bill E.


Nov 13 '05 #6

P: n/a
I'm not sure that my post went through, so I'll repeat myself.

Some thoughts:

*Can you try adding a semicolon at the end of the string as in

strConnection = "Provider=sqloledb;DataSource=server02;User
Id=sa;Password=qr7959;Initial Catalog=SubscriberMngt;"

*Are you sure that qr7959 has no capitalization? Have you tried QR7959
or Qr7959 instead?

*Can you ping server02? Can you ping its IP address? Have you tried
replacing server02 with the IP address?

Bill

Nov 13 '05 #7

P: n/a
I suppose one thing is that you're not terminating your connection
string with a semicolon. I'm not sure if it's necessary, but can you
try changing

"Initial Catalog=SubscriberMngt"

to

"Initial Catalog=SubscriberMngt;"

Lastly, I'm not sure if there is case sensitivity with passwords, but
have you tried different variations of br8111 as in BR8111 or Br8111?

Bill

Nov 13 '05 #8

P: n/a
Hi there,

Thanks for the suggestions but no luck :-(. I'm going to try using a
different account i.e. not the "sa" account but would appreciate a post
if anyone out there knows why the "sa" account wouldn't work in this
instance. If I get the same problem with a Windows account, I'll know
that it's not an account issue but perhaps a permissions issue? It
shouldn't have to be so complicated ...

Thanks again,

Barb

billmia...@netscape.net wrote:
I suppose one thing is that you're not terminating your connection
string with a semicolon. I'm not sure if it's necessary, but can you
try changing

"Initial Catalog=SubscriberMngt"

to

"Initial Catalog=SubscriberMngt;"

Lastly, I'm not sure if there is case sensitivity with passwords, but
have you tried different variations of br8111 as in BR8111 or Br8111?

Bill


Nov 13 '05 #9

P: n/a
Hi again,

I just wanted to let everyone know that I finally got it to work by
using integrated security. I know that this is the recommended method
but I'd still be curious to know if anyone had any further thoughts on
why it didn't work with "sa".

Barb

Barb wrote:
Hi there,

Thanks for the suggestions but no luck :-(. I'm going to try using a
different account i.e. not the "sa" account but would appreciate a post if anyone out there knows why the "sa" account wouldn't work in this
instance. If I get the same problem with a Windows account, I'll know that it's not an account issue but perhaps a permissions issue? It
shouldn't have to be so complicated ...

Thanks again,

Barb

billmia...@netscape.net wrote:
I suppose one thing is that you're not terminating your connection
string with a semicolon. I'm not sure if it's necessary, but can you try changing

"Initial Catalog=SubscriberMngt"

to

"Initial Catalog=SubscriberMngt;"

Lastly, I'm not sure if there is case sensitivity with passwords, but have you tried different variations of br8111 as in BR8111 or Br8111?
Bill


Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.