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.*

[Dave Anderson]

"Middletree" wrote:

>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.*

SQL Server Management Studio
Right-click on server, select Properties
Click on Security
Set authentication to desired mode


--
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.

[Dave Anderson]

"Middletree" wrote:

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.

Use these instructions to create a data link (UDL file):
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Proceed to the "Test Connection" part. If you succeed in connecting, click
OK and elect to save. Then open the file in notepad (UDL files are just text
files, after all). You have your connection string.


--
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]

Thanks!

SQL Server Management Studio
Right-click on server, select Properties
Click on Security
Set authentication to desired mode

[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.

>
Use these instructions to create a data link (UDL file):
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Proceed to the "Test Connection" part. If you succeed in connecting, click
OK and elect to save. Then open the file in notepad (UDL files are just
text files, after all). You have your connection string.


--
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.

[Dave Anderson]

Middletree wrote:

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?
--
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]

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.

>
Are you getting the "not a trusted connection" error when configuring the
UDL and pressing [Test Connection], or when using the connection string?
--
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]

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]

Middletree wrote:

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

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

[Dave Anderson]

I wrote:

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]

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
"Dave Anderson" <NP**********@spammotel.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

>I wrote:

>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.

[Dave Anderson]

[please do not toppost]

"Middletree" wrote:

>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.

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

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."


--
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.

[Dave Anderson]

"middletree" wrote:

But this was local.

OK. I had to ask.

Are you able to connect with ANY tool (like a data link) using a SQL Server
Login and password, or can you only connect using Windows Authentication?

--
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]

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]

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[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]

Middletree wrote:

>[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.


--
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]

>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.