Connection string confusion

[Middletree]

I have never really understood connection strings. I'm not sure why, as I'm
a smart guy otherwise, but I cannot make sense of any of the explanations I
have seen out there over the past 7 or so years that I have been doing ASP.
I have somehow just managed to get by with copying existing connections
strings, and making changes until it works.

This time, however, I am at a loss. I have an ASP 3 app, which is on my
local machine (WinXP Pro) for now, but will be uploaded to a real host for
viewing my on a members-only site. It will use SQL Server 2005. I had it
working fine with Access, but I had to move the db over to SQL Server,
because that's what it's going to be in Production.

I have tried variations of the different attempts you can see below. I have
read www.connectionstrings.com and www.aspfaq.com.

I currently have SQL Server set for Windows Authentication. I have no
problem setting it for Mixed Mode, but have not figured out why.

I have verified that IUSR_(machinename) is a user on the machine.

The errors I get, depending on which of the various attempts I make, are
usually some sort of permissions error. Any help appreciated.

strDBConnection = _

"Provider=SQLOLEDB;Persist Security Info=False;Data Source=machinename;" &
_

"User ID=userid;Password=password;Database=1;"

Dim objConnection

Set objConnection = Server.CreateObject("ADODB.Connection")

objConnection.Open strDBConnection




and



strDBConnection = "Provider=SQLOLEDB;" & _

"Data Source=<machinename>\<SQLEXPRESS>;" & _

"Initial Catalog=<shape>;" & _

"Integrated Security=SSPI"


set conn = CreateObject("ADODB.Connection")

conn.open strDBConnection



and



set db = Server.CreateObject("ADODB.Connection")

strConnect = "Provider=SQLOLEDB; Data Source=machinename; Initial
Catalog=shape; User ID=userid; Password=password; Network Library=dbmssocn;"

and

strConnect = "Provider=SQLOLEDB;Driver=SQL
Server;Server=machinename;Database=shape;"

db.Open strConnect

 

[Middletree]

I currently have SQL Server set for Windows Authentication. I have no

problem setting it for Mixed Mode, but have not figured out why.

that should say:

but have not figured out *how.*

 

[Middletree]

Thanks!

 

[Middletree]

Perhaps this is a SQL Server question, but I cannot seem to make this work,
because it says that the user I created is not a trusted SQL Server
connection. I created a new user and login and am trying to use that, but I
get that error when I try to go through the UDL steps and point to that
user.

 

[Dave Anderson]

Perhaps this is a SQL Server question, but I cannot seem to make
this work, because it says that the user I created is not a
trusted SQL Server connection. I created a new user and login and
am trying to use that, but I get that error when I try to go
through the UDL steps and point to that user.

Are you getting the "not a trusted connection" error when configuring the
UDL and pressing [Test Connection], or when using the connection string?

 

[Middletree]

When configuring the UDL. I never get to the point of testing the
connection. As sson as I try to select a default database, I get a popup
with the error.

Note that for testing purposes, I created, just this morning, a login called
"asp" with a password of "asp" (I'll make it more secure later; just want to
get the process down). I then got this error. So in the specific database I
am using, I opened "Security" and created a user and tied it to that login.
SQL Server had no problem accepting this. I only got hte error when I went
back to the UDL screen.

 

[Middletree]

This is weird. When I changed the UDL to Windows authentication, the "Test"
button worked fine, but the connection string, when used in the ASP page,
still gave me a "Cannot open database "SHAPE" requested by the login. The
login failed." message.

 

[Dave Anderson]

This is weird. When I changed the UDL to Windows authentication, the
"Test" button worked fine, but the connection string, when used in
the ASP page, still gave me a "Cannot open database "SHAPE" requested
by the login. The login failed." message.

That's because you are using Windows Authentication. I thought you said you
were going to set it to "mixed".

 

[Dave Anderson]

That's because you are using Windows Authentication. I thought
you said you were going to set it to "mixed".

I should add that (1) I assume you are NOT trying this with SQL Server 2005
Express, Evaluation or Developer editions (see:
http://msdn2.microsoft.com/en-us/library/ms178763.aspx), and (2) you have
already done the [User Mapping] for the SQL Server login used in the
connection.

To be absolutely certain, connect to the database with the Management
Studio, open [Security], and look at the properties for the Login. Clike
[User Mapping], and ensure that the DB in question is checked.

 

[Middletree]

I did set it to Mixed, and I am using Express.

Doing some Googling, I am finding that a lot of people are having trouble
connecting to Express.

The User Mapping does have the correct DB checked.

I tried to connect to SQL Server using the new user I created, and I got the
"not trusted" error again. I think the problem is with SQL Server, not my
ASP connection string.

thanks

That's because you are using Windows Authentication. I thought
you said you were going to set it to "mixed".

I should add that (1) I assume you are NOT trying this with SQL Server
2005 Express, Evaluation or Developer editions (see:
http://msdn2.microsoft.com/en-us/library/ms178763.aspx), and (2) you have
already done the [User Mapping] for the SQL Server login used in the
connection.

To be absolutely certain, connect to the database with the Management
Studio, open [Security], and look at the properties for the Login. Clike
[User Mapping], and ensure that the DB in question is checked.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message.
Use of this email address implies consent to these terms.

 

[middletree]

Well, did you look at the link I provided about it? Ahem:

"By default, SQL Server 2005 Express, Evaluation, and
Developer editions allow local client connections only."

But this was local.

 

[Middletree]

Sorry it's been a while since I got back on this. I am out of town on
business and have been for several days.

I appreciate your help and suggestions. I suspected that it might be
something to do with SQL Server Express, so I uninstalled it and installed
SQL Server Standard. It works just fine now.

thanks

 

[Evertjan.]

Middletree wrote on 03 jun 2007 in
microsoft.public.inetserver.asp.general:

Sorry it's been a while since I got back on this. I am out of town on
business and have been for several days.

I appreciate your help and suggestions. I suspected that it might be
something to do with SQL Server Express, so I uninstalled it and
installed SQL Server Standard. It works just fine now.

Seems you are responding to someone on something, thinking this is email.

[please always quote on usenet]

 

[Middletree]

Seems you are responding to someone on something, thinking this is email.

[please always quote on usenet]

I do not think this is email. Mind your own business.

 

[Dave Anderson]

[please always quote on usenet]

I do not think this is email. Mind your own business.

No need to be upset with Evertjan. I would not have recognized your post as
part of my conversation with you if he had said nothing. The manner with
which he makes his point may not be optimal, I agree.

 

[Middletree]

The manner with which he makes his point may not be optimal, I agree.
I don't agree with his method of making his point or his point. The rules
against top-posting, while long entrenched, are silly. Most people I know
prefer to read top-posted replies. It's certainly not a consensus. And in
this case, it's another unwritten rule against replying without quoting. But
most of all, it seems he has come into a thread he wasn't part of, and
corrected my actions, in a rude way, to boot.