Connection string confusion

Discussion in 'ASP General' started by Middletree, May 24, 2007.

  1. Middletree

    Middletree Guest

    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, May 24, 2007
    #1
    1. Advertising

  2. Middletree

    Middletree Guest

    > 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, May 24, 2007
    #2
    1. Advertising

  3. Middletree

    Middletree Guest

    Thanks!


    > SQL Server Management Studio
    > Right-click on server, select Properties
    > Click on Security
    > Set authentication to desired mode
     
    Middletree, May 25, 2007
    #3
  4. Middletree

    Middletree Guest

    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.
     
    Middletree, May 25, 2007
    #4
  5. 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.
     
    Dave Anderson, May 25, 2007
    #5
  6. Middletree

    Middletree Guest

    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, May 25, 2007
    #6
  7. Middletree

    Middletree Guest

    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.
     
    Middletree, May 25, 2007
    #7
  8. 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, May 25, 2007
    #8
  9. 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, May 25, 2007
    #9
  10. Middletree

    Middletree Guest

    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:%...
    >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, May 25, 2007
    #10
  11. Middletree

    middletree Guest

    > 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, May 27, 2007
    #11
  12. Middletree

    Middletree Guest

    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
     
    Middletree, Jun 3, 2007
    #12
  13. Middletree

    Evertjan. Guest

    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)
     
    Evertjan., Jun 3, 2007
    #13
  14. Middletree

    Middletree Guest


    > 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.
     
    Middletree, Jun 7, 2007
    #14
  15. 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.
     
    Dave Anderson, Jun 7, 2007
    #15
  16. Middletree

    Middletree Guest

    >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.
     
    Middletree, Jun 7, 2007
    #16
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gaurav
    Replies:
    0
    Views:
    16,199
    Gaurav
    Nov 7, 2003
  2. =?Utf-8?B?TWlrZSBNb29yZQ==?=

    Connection String object Convert to String Variable Type

    =?Utf-8?B?TWlrZSBNb29yZQ==?=, Oct 26, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    1,089
    Norman Yuan
    Oct 26, 2004
  3. Goh, Yong Kwang
    Replies:
    3
    Views:
    11,876
    Barry Schwarz
    Apr 3, 2004
  4. Replies:
    3
    Views:
    864
    Samuel R. Neff
    Aug 3, 2007
  5. Wei  Lu
    Replies:
    2
    Views:
    6,010
    Wei Lu
    Feb 16, 2009
Loading...

Share This Page