Hi All,

Excuse me if this has been covered here before.

I have an ASP application that throws an error '80040e4d':

Invalid Connection String Attribute

Here is the DSN:
"Provider=SQLOLEDB.1;Persist Security Info=False;DSN=ProjectMan;UID=ProjectMan;Password= xxxxxxxxxx;Database=MasterProjectSQL;"

If I take out "Provider=SLQOLEDB.1;Persist Security Info=False;" out of the DSN it works fine. All my other DSNs work fine to other databases on the same server, in the same application, but not to the 'MasterProjectSQL' database.

Any ideas on what the problem is or how to configure the database to use this provider? Or should I even be using this provider?

I've read as much as I can find on this error and haven't seen this particular error addressed anywhere.....yet.

Thanks in advance for any info you have on this.

Richard

For more details on connection string you can refer www.connectionstrings.com

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks HappyGV for your reply. However, I didn't see anything there that addesses the specific problem I'm having. I may have missed what you are referring to.

All of my DSNs work, even the one listed above as long as I don't use "Provider=SQLOLEDB.1;Persist Security Info=False;" in the connection string. The other databases on this same server function properly with that in the connection string to those other databases.

Do you know of any way to configure a database to use specific providers?

Thank you,
Richard

What is the database server that you use? Is there any reason why you wanted to use those attributes?

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

The database server is MS SQL 2000 SP3a. And to tell you the truth I don't even know if I need to use those params or why they are used. My understanding, maybe somewhat misguided, is that the OLEDB layer is a faster more direct connection to the server, more efficient, etc. I think I copied that code from somewhere years ago and have been using it ever since. I'm havning trouble even finding any information about this syntax. I'm looking for enlightenment. What's most perplexing is that this syntax works on all of the databases on the same server except for one. It would be very simple to just not use that syntax which I'm doing now but would really like to know what this syntax is used for and what it does.

Thanks for your reply. Much appreciated.

Richard

Hi Richard,

Sorry about the delayed replyto this.

I am not sure why you are trying to use DSN along with Provider. You should use either DSN or Provider.

This is how it should be if you opt not to use DSN.
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist_
             Security Info=False;Initial Catalog=YourDB;_
             Data Source=YOUR_LOCAL_SERVER_NAME"

It looks like you are mixing up both.

Here is the information on how persist security is used and why, that I read from Microsoft site.
Keep Persist Security Info as False
Setting thePersist Security Info keyword to true or yes in a connection string will allow security-sensitive information, including the user ID and password, to be obtained from the connection after the connection has been opened. If you must supply a user ID and password when making a connection, you are most protected if that information is used to open the connection and then discarded, as is the case when Persist Security Info is set to false or no. This is especially important if you are supplying an open connection to an untrusted source or persisting connection information to disk. Keeping Persist Security Info as false helps ensure that the untrusted source does not have access to the security-sensitive information for your connection and also helps ensure that no security-sensitive information is persisted to disk with your connection string information.

Persist Security Info is set to false by default.
Hope that explains.

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

Thanks for the reply.

As I mentioned in an earlier post on this topic, I had copied this connection string years ago when I first started programming and never thought much about it since it always worked. When I started getting the "invalid connection string" error I wanted to find out what was going on here. Why would this connection string error on one SQL server and not the other and also, like you suggest, do I even need this parameter when using a DSN?

So, thank you so much for the reply. According to what you are saying, you don't need to define a provider in a DSN. OK, now I'm like the little kid that gets more questions after having the first one answered. Why don't you need to specify a provider in a DSN? Or, more importantly, if you can't specify a provider, which provider is used by default? Do I have it wrong, I was taught that OLEDB was the fastest provider, is that true?

Also, the information you posted says, " If you must supply a user ID and password when making a connection, you are most protected if that information is used to open the connection and then discarded, as is the case when Persist Security Info is set to false or no." I am supplying a username and password in my DSNs and would like not to persist security info. And why does the "Persist..." not throw an error on some servers and not on others?

Anyway, I've probably got it all screwed around sideways. I appreciate the dialogue. I want to learn it and not just get someone to answer it for me. Although, the answer is nice too, hehe.

Stay happy.

Thanks again.

Richard

Hmmm... that's too many questions to be answered. Well I can undestand that.

DSN is something that you configure locally and have it named, which would have all the information that you use to connect to a db, the driver/provider, user name, password, db, server etc... Now when you use that for connecting to a db, you got to provide just the DSN and minimal required details along with it. Moreover DSN is configured on the system that hosts the website. Anytime the webserver changed or website is shifted, then DSN too got to be shifted along with it. DSN can be configured to use username/password or even without that. This method was old way of connecting to db.

Now every db server has its own native provider. Say for SQL server you have SQLOLEDB provider which is native to sql server and does have better performance, than using something like odbc/DSN or any other connectivity method. IMO, DSN and Provider are different from one another in terms of usage and performance. So it shouldn't be mixed and get confused.

I would suggest you read about usage of DSN and providers for db connectivity.
You can take a look at the following link to see how the connection looks with/without DSN.
http://www.webhoster.ca/webdatabase/dsnconnection.asp

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

Yeah, unfortunately I many questions in my striving for perfection.

Thanks a million for your responses. Much appreciated.

rstelma