Connection string confusion

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=\;" & _

"Initial Catalog=;" & _

"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

Re: Connection string confusion

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

Re: Connection string confusion

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

Re: Connection string confusion

"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/library/aa140076(office.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.

Re: Connection string confusion

Thanks!


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

Re: Connection string confusion

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/library/aa140076(office.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.

Re: Connection string confusion

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.

Re: Connection string confusion

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

Re: Connection string confusion

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.

Re: Connection string confusion

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.

Re: Connection string confusion

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.

Re: Connection string confusion

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" wrote in message
news:%236t59ttnHHA.1240@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.
>

Re: Connection string confusion

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

Re: Connection string confusion

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

Re: Connection string confusion

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

Re: Connection string confusion

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

Re: Connection string confusion

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)

Re: Connection string confusion

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

Re: Connection string confusion

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.

Re: Connection string confusion

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