ASP Access to SQL SERVER change

Discussion in 'ASP General' started by Bob and Sharon Hiller, Mar 15, 2006.

  1. I have an ASP page that was done in VBScript
    It is setup to read an Access database and I need to change it to read
    a Sql 2005 Database.

    The code that is used to open the Access Database:

    Set adoConnection = server.CreateObject("ADODB.Connection")
    Set adoRecordset = server.CreateObject("ADODB.Recordset")
    adoConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
    Dim strLocation, iLength
    strLocation = Request.ServerVariables("PATH_TRANSLATED")
    iLength = Len(strLocation)
    iLength = iLength - 11
    strLocation = Left(strLocation, iLength)
    strLocation = strLocation & "../Database.mdb"
    adoConnection.Open ("Data Source=" & strLocation)
    adoRecordset.ActiveConnection = AdoConnection


    In my VB 6.0 app I use the following to open the SQL Database"

    Set DataBaseTS_1 = New ADODB.Connection
    DataBaseTS_1.ConnectionString ="Provider=MSDASQL.1;Persist Security
    Info=False;Extended
    Properties=Description=Large Pump Data Source;DRIVER=SQL
    Server;SERVER=LPDATASYSTEM\PL3LP;APP=Microsoft Data Access
    Components;WSID=LPDATASYSTEM;DATABASE=LargePump;Trusted_Connection=Yes;Initi
    al Catalog=LargePump"

    DataBaseTS_1.Open

    How can I get the VBScript to open the SQL Database?

    Thanks,
    Bob Hiller
    Lifts for the Disabled LLC
    Bob and Sharon Hiller, Mar 15, 2006
    #1
    1. Advertising

  2. Bob and Sharon Hiller wrote:
    > I have an ASP page that was done in VBScript
    > It is setup to read an Access database and I need to change it
    > to read a Sql 2005 Database.

    <snip>
    > How can I get the VBScript to open the SQL Database?
    >

    http://www.aspfaq.com/show.asp?id=2126
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 15, 2006
    #2
    1. Advertising

  3. Here is what I have tried: No records are returned(This table has 2094 rows,
    28 columns)

    Set adoConnection = server.CreateObject("ADODB.Connection")
    Set adoRecordset = server.CreateObject("ADODB.Recordset")
    adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
    & "Persist Security Info=False;" _
    & "Extended Properties=Description=Large Pump Data Source;" _
    & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
    & "APP=Microsoft Data Access Components;" _
    & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
    & "Trusted_Connection=Yes;Initial Catalog=LargePump"
    adoConnection.Open

    adoRecordset.ActiveConnection = adoConnection
    Dim SqlSelect
    SqlSelect = "select * from [LargePumpFloor_TS1] order by Row_num"
    adoRecordset.CursorLocation = 3
    adoRecordset.CursorType = 3
    call adoRecordset.Open(SQLSelect)
    adoRecordset.PageSize = 12
    adoRecordset.CacheSize = adoRecordset.PageSize
    intPageCount = adoRecordset.PageCount
    intRecordCount = adoRecordset.RecordCount

    Do you see anything ? I am running IIS on Windows XP Pro with MS
    SQL Server 2005. I can access and see any MS Access Database but no SQL
    databases.

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Bob and Sharon Hiller wrote:
    >> I have an ASP page that was done in VBScript
    >> It is setup to read an Access database and I need to change it
    >> to read a Sql 2005 Database.

    > <snip>
    >> How can I get the VBScript to open the SQL Database?
    >>

    > http://www.aspfaq.com/show.asp?id=2126
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Bob and Sharon Hiller, Mar 15, 2006
    #3
  4. Bob and Sharon Hiller wrote:
    > Here is what I have tried: No records are returned(This table has
    > 2094 rows, 28 columns)
    >
    > Set adoConnection = server.CreateObject("ADODB.Connection")
    > Set adoRecordset = server.CreateObject("ADODB.Recordset")
    > adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
    > & "Persist Security Info=False;" _
    > & "Extended Properties=Description=Large Pump Data Source;" _
    > & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
    > & "APP=Microsoft Data Access Components;" _
    > & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
    > & "Trusted_Connection=Yes;Initial Catalog=LargePump"


    Nothing to do with your problem, but you should avoid odbc. See the link in
    my original reply.

    > adoConnection.Open
    >
    > adoRecordset.ActiveConnection = adoConnection
    > Dim SqlSelect
    > SqlSelect = "select * from [LargePumpFloor_TS1] order by Row_num"
    > adoRecordset.CursorLocation = 3
    > adoRecordset.CursorType = 3
    > call adoRecordset.Open(SQLSelect)
    > adoRecordset.PageSize = 12
    > adoRecordset.CacheSize = adoRecordset.PageSize
    > intPageCount = adoRecordset.PageCount
    > intRecordCount = adoRecordset.RecordCount
    >
    > Do you see anything ? I am running IIS on Windows XP Pro with MS
    > SQL Server 2005. I can access and see any MS Access Database but no
    > SQL databases.
    >



    I see no attempt to check the recordset's EOF property. How are you
    determining that no records were returned?

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 15, 2006
    #4
  5. Bob,
    After
    intRecordCount = adoRecordset.RecordCount

    I have:
    If intRecordCount <> 0 Then
    Response.Write("Record Count <> 0")
    Else
    Response.Write("Record Count = 0")
    End If

    I also tried:
    intRowsCount = adoRecordset.GetRows
    If intRowsCount <> 0 Then
    Response.Write("Rows Count <> 0")
    Else
    Response.Write("Rows Count = 0")
    End If

    Looking at the link you sent, it appears that I would need to totally
    reconfigure the SQL server to use that method.

    Thanks,
    Bob Hiller
    Lifts for the Disabled LLC

    "Bob Barrows [MVP]" <> wrote in message
    news:ue3OW%...
    > Bob and Sharon Hiller wrote:
    >> Here is what I have tried: No records are returned(This table has
    >> 2094 rows, 28 columns)
    >>
    >> Set adoConnection = server.CreateObject("ADODB.Connection")
    >> Set adoRecordset = server.CreateObject("ADODB.Recordset")
    >> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
    >> & "Persist Security Info=False;" _
    >> & "Extended Properties=Description=Large Pump Data Source;" _
    >> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
    >> & "APP=Microsoft Data Access Components;" _
    >> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
    >> & "Trusted_Connection=Yes;Initial Catalog=LargePump"

    >
    > Nothing to do with your problem, but you should avoid odbc. See the link
    > in
    > my original reply.
    >
    >> adoConnection.Open
    >>
    >> adoRecordset.ActiveConnection = adoConnection
    >> Dim SqlSelect
    >> SqlSelect = "select * from [LargePumpFloor_TS1] order by Row_num"
    >> adoRecordset.CursorLocation = 3
    >> adoRecordset.CursorType = 3
    >> call adoRecordset.Open(SQLSelect)
    >> adoRecordset.PageSize = 12
    >> adoRecordset.CacheSize = adoRecordset.PageSize
    >> intPageCount = adoRecordset.PageCount
    >> intRecordCount = adoRecordset.RecordCount
    >>
    >> Do you see anything ? I am running IIS on Windows XP Pro with MS
    >> SQL Server 2005. I can access and see any MS Access Database but no
    >> SQL databases.
    >>

    >
    >
    > I see no attempt to check the recordset's EOF property. How are you
    > determining that no records were returned?
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Bob and Sharon Hiller, Mar 16, 2006
    #5
  6. Bob and Sharon Hiller

    Mike Brind Guest

    Bob and Sharon Hiller wrote:
    > Bob,
    > After
    > intRecordCount = adoRecordset.RecordCount
    >
    > I have:
    > If intRecordCount <> 0 Then
    > Response.Write("Record Count <> 0")
    > Else
    > Response.Write("Record Count = 0")
    > End If
    >
    > I also tried:
    > intRowsCount = adoRecordset.GetRows
    > If intRowsCount <> 0 Then
    > Response.Write("Rows Count <> 0")
    > Else
    > Response.Write("Rows Count = 0")
    > End If


    GetRows returns an array, not a numeric value.

    arrRows = adoRecordset.GetRows()
    If isarray(arrRows) Then
    intRowsCount = ubound(arrRows,2)
    response.write "Total Rows = " & intRowsCount +1
    Else
    response.write "No Records Found
    End If

    --
    Mike Brind
    Mike Brind, Mar 16, 2006
    #6
  7. "Mike Brind" <> wrote in message
    news:...
    >
    > Bob and Sharon Hiller wrote:
    > > Bob,
    > > After
    > > intRecordCount = adoRecordset.RecordCount
    > >
    > > I have:
    > > If intRecordCount <> 0 Then
    > > Response.Write("Record Count <> 0")
    > > Else
    > > Response.Write("Record Count = 0")
    > > End If
    > >
    > > I also tried:
    > > intRowsCount = adoRecordset.GetRows
    > > If intRowsCount <> 0 Then
    > > Response.Write("Rows Count <> 0")
    > > Else
    > > Response.Write("Rows Count = 0")
    > > End If

    >
    > GetRows returns an array, not a numeric value.
    >
    > arrRows = adoRecordset.GetRows()
    > If isarray(arrRows) Then
    > intRowsCount = ubound(arrRows,2)
    > response.write "Total Rows = " & intRowsCount +1
    > Else
    > response.write "No Records Found
    > End If
    >
    > --
    > Mike Brind
    >


    Problem is GetRows will error if there are no rows. Use:

    If not adoRecordset.EOF Then
    arrRows = adoRecordset.GetRows()
    End If

    If isarray(arrRows) Then
    intRowsCount = ubound(arrRows,2)
    response.write "Total Rows = " & intRowsCount +1
    Else
    response.write "No Records Found
    End

    Anthony.
    Anthony Jones, Mar 16, 2006
    #7
  8. Bob and Sharon Hiller wrote:
    > Bob,
    > After
    > intRecordCount = adoRecordset.RecordCount
    >

    With a default server-side, forward-only cursor, Recordcount will always
    contain -1.
    http://www.aspfaq.com/show.asp?id=2193
    As this article says, there are better ways to count the records returned by
    a query.

    >
    > I also tried:
    > intRowsCount = adoRecordset.GetRows


    Mike addressed this one.
    >
    > Looking at the link you sent, it appears that I would need to totally
    > reconfigure the SQL server to use that method.


    Why? Where does that article say anything about reconfiguring SQL Server if
    you don't have to? Simply change your connection string to the one he
    suggests using for integrated (Windows) security. Here, let me show you:

    >>> Set adoConnection = server.CreateObject("ADODB.Connection")
    >>> Set adoRecordset = server.CreateObject("ADODB.Recordset")
    >>> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
    >>> & "Persist Security Info=False;" _
    >>> & "Extended Properties=Description=Large Pump Data Source;" _
    >>> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
    >>> & "APP=Microsoft Data Access Components;" _
    >>> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
    >>> & "Trusted_Connection=Yes;Initial Catalog=LargePump"

    >>

    adoConnection.ConnectionString = "Provider=SQLOLEDB;" _
    & "Persist Security Info=False;" _
    & "Data Source=LPDATASYSTEM\PL3LP;" _
    & "Application Name=Microsoft Data Access Components;" _
    & "Integrated Security=SSP1;Initial Catalog=LargePump"

    'I would suggest setting the Application Name to a more specific name rather
    than the generic "Microsoft ... ". This will allow debugging using SQL
    Profiler to be easier (you can set up a trace using a filter to display only
    a specific application).

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 16, 2006
    #8
  9. I am starting to think that there is no way to connect to SQL 2005 with
    VBScript. I have tried 25 different suggestions from news groups and forums
    and none have worked. The only thing that seems to work is report services
    using .net and we are not going to change every page we have. I think we
    will just go back to SQL 2000 where everything worked fine.

    Thanks for the help.

    Bob Hiller


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Bob and Sharon Hiller wrote:
    >> Bob,
    >> After
    >> intRecordCount = adoRecordset.RecordCount
    >>

    > With a default server-side, forward-only cursor, Recordcount will always
    > contain -1.
    > http://www.aspfaq.com/show.asp?id=2193
    > As this article says, there are better ways to count the records returned
    > by
    > a query.
    >
    >>
    >> I also tried:
    >> intRowsCount = adoRecordset.GetRows

    >
    > Mike addressed this one.
    >>
    >> Looking at the link you sent, it appears that I would need to totally
    >> reconfigure the SQL server to use that method.

    >
    > Why? Where does that article say anything about reconfiguring SQL Server
    > if
    > you don't have to? Simply change your connection string to the one he
    > suggests using for integrated (Windows) security. Here, let me show you:
    >
    >>>> Set adoConnection = server.CreateObject("ADODB.Connection")
    >>>> Set adoRecordset = server.CreateObject("ADODB.Recordset")
    >>>> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
    >>>> & "Persist Security Info=False;" _
    >>>> & "Extended Properties=Description=Large Pump Data Source;" _
    >>>> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
    >>>> & "APP=Microsoft Data Access Components;" _
    >>>> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
    >>>> & "Trusted_Connection=Yes;Initial Catalog=LargePump"
    >>>

    > adoConnection.ConnectionString = "Provider=SQLOLEDB;" _
    > & "Persist Security Info=False;" _
    > & "Data Source=LPDATASYSTEM\PL3LP;" _
    > & "Application Name=Microsoft Data Access Components;" _
    > & "Integrated Security=SSP1;Initial Catalog=LargePump"
    >
    > 'I would suggest setting the Application Name to a more specific name
    > rather
    > than the generic "Microsoft ... ". This will allow debugging using SQL
    > Profiler to be easier (you can set up a trace using a filter to display
    > only
    > a specific application).
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Bob and Sharon Hiller, Mar 16, 2006
    #9
  10. Nobody can help you if you just throw up your hands instead of describing
    your symptoms.





    Bob and Sharon Hiller wrote:
    > I am starting to think that there is no way to connect to SQL 2005
    > with VBScript. I have tried 25 different suggestions from news groups
    > and forums and none have worked. The only thing that seems to work is
    > report services using .net and we are not going to change every page
    > we have. I think we will just go back to SQL 2000 where everything
    > worked fine.
    >
    > Thanks for the help.
    >
    > Bob Hiller
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> Bob and Sharon Hiller wrote:
    >>> Bob,
    >>> After
    >>> intRecordCount = adoRecordset.RecordCount
    >>>

    >> With a default server-side, forward-only cursor, Recordcount will
    >> always contain -1.
    >> http://www.aspfaq.com/show.asp?id=2193
    >> As this article says, there are better ways to count the records
    >> returned by
    >> a query.
    >>
    >>>
    >>> I also tried:
    >>> intRowsCount = adoRecordset.GetRows

    >>
    >> Mike addressed this one.
    >>>
    >>> Looking at the link you sent, it appears that I would need to
    >>> totally reconfigure the SQL server to use that method.

    >>
    >> Why? Where does that article say anything about reconfiguring SQL
    >> Server if
    >> you don't have to? Simply change your connection string to the one he
    >> suggests using for integrated (Windows) security. Here, let me show
    >> you:
    >>
    >>>>> Set adoConnection = server.CreateObject("ADODB.Connection")
    >>>>> Set adoRecordset = server.CreateObject("ADODB.Recordset")
    >>>>> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
    >>>>> & "Persist Security Info=False;" _
    >>>>> & "Extended Properties=Description=Large Pump Data Source;" _
    >>>>> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
    >>>>> & "APP=Microsoft Data Access Components;" _
    >>>>> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
    >>>>> & "Trusted_Connection=Yes;Initial Catalog=LargePump"
    >>>>

    >> adoConnection.ConnectionString = "Provider=SQLOLEDB;" _
    >> & "Persist Security Info=False;" _
    >> & "Data Source=LPDATASYSTEM\PL3LP;" _
    >> & "Application Name=Microsoft Data Access Components;" _
    >> & "Integrated Security=SSP1;Initial Catalog=LargePump"
    >>
    >> 'I would suggest setting the Application Name to a more specific name
    >> rather
    >> than the generic "Microsoft ... ". This will allow debugging using
    >> SQL Profiler to be easier (you can set up a trace using a filter to
    >> display only
    >> a specific application).
    >>
    >> Bob Barrows
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 16, 2006
    #10
  11. Bob,
    I think I know what may be happening. I believe that you must use a User ID
    and Password to connect to the DB in ASP,VBScript. I believe this because
    every connection string that uses a trusted connection works fine in VB 6.0.
    If I try to use a connection string in VB 6.0 that uses User ID and PassWord
    I get the following error:
    (Login failed for user '<LoginName>'. Reason: Not associated with a trusted
    SQL Server connection)

    I have tried every suggestion from MS website on how to setup a User ID and
    Password that is associated with a trusted SQL Server connection but cannot
    make one work. I have SQL 2005 setup in authentication mode = SQL Server
    and Windows

    Sincerly,
    Bob Hiller


    "Bob Barrows [MVP]" <> wrote in message
    news:%236L%...
    > Nobody can help you if you just throw up your hands instead of describing
    > your symptoms.
    >
    >
    >
    >
    >
    > Bob and Sharon Hiller wrote:
    >> I am starting to think that there is no way to connect to SQL 2005
    >> with VBScript. I have tried 25 different suggestions from news groups
    >> and forums and none have worked. The only thing that seems to work is
    >> report services using .net and we are not going to change every page
    >> we have. I think we will just go back to SQL 2000 where everything
    >> worked fine.
    >>
    >> Thanks for the help.
    >>
    >> Bob Hiller
    >>
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >>> Bob and Sharon Hiller wrote:
    >>>> Bob,
    >>>> After
    >>>> intRecordCount = adoRecordset.RecordCount
    >>>>
    >>> With a default server-side, forward-only cursor, Recordcount will
    >>> always contain -1.
    >>> http://www.aspfaq.com/show.asp?id=2193
    >>> As this article says, there are better ways to count the records
    >>> returned by
    >>> a query.
    >>>
    >>>>
    >>>> I also tried:
    >>>> intRowsCount = adoRecordset.GetRows
    >>>
    >>> Mike addressed this one.
    >>>>
    >>>> Looking at the link you sent, it appears that I would need to
    >>>> totally reconfigure the SQL server to use that method.
    >>>
    >>> Why? Where does that article say anything about reconfiguring SQL
    >>> Server if
    >>> you don't have to? Simply change your connection string to the one he
    >>> suggests using for integrated (Windows) security. Here, let me show
    >>> you:
    >>>
    >>>>>> Set adoConnection = server.CreateObject("ADODB.Connection")
    >>>>>> Set adoRecordset = server.CreateObject("ADODB.Recordset")
    >>>>>> adoConnection.ConnectionString = "Provider=MSDASQL.1;" _
    >>>>>> & "Persist Security Info=False;" _
    >>>>>> & "Extended Properties=Description=Large Pump Data Source;" _
    >>>>>> & "DRIVER=SQL Server;SERVER=LPDATASYSTEM\PL3LP;" _
    >>>>>> & "APP=Microsoft Data Access Components;" _
    >>>>>> & "WSID=LPDATASYSTEM;DATABASE=LargePump;" _
    >>>>>> & "Trusted_Connection=Yes;Initial Catalog=LargePump"
    >>>>>
    >>> adoConnection.ConnectionString = "Provider=SQLOLEDB;" _
    >>> & "Persist Security Info=False;" _
    >>> & "Data Source=LPDATASYSTEM\PL3LP;" _
    >>> & "Application Name=Microsoft Data Access Components;" _
    >>> & "Integrated Security=SSP1;Initial Catalog=LargePump"
    >>>
    >>> 'I would suggest setting the Application Name to a more specific name
    >>> rather
    >>> than the generic "Microsoft ... ". This will allow debugging using
    >>> SQL Profiler to be easier (you can set up a trace using a filter to
    >>> display only
    >>> a specific application).
    >>>
    >>> Bob Barrows
    >>> --
    >>> Microsoft MVP -- ASP/ASP.NET
    >>> Please reply to the newsgroup. The email account listed in my From
    >>> header is my spam trap, so I don't check it very often. You will get
    >>> a quicker response by posting to the newsgroup.

    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Bob and Sharon Hiller, Mar 16, 2006
    #11
  12. Bob and Sharon Hiller wrote:
    > (Login failed for user '<LoginName>'. Reason: Not associated with a
    > trusted SQL Server connection)
    >
    > I have tried every suggestion from MS website on how to setup a User
    > ID and Password that is associated with a trusted SQL Server
    > connection but cannot make one work. I have SQL 2005 setup in
    > authentication mode = SQL Server and Windows


    Check out http://www.connectionstrings.com/

    In particular, look in the SQL Server (not 2005) "read more" section for
    details on forcing a TCP/IP connection.

    When I was having similar trouble with some SQL Server 2000 connections,
    this resolved it. For some reason, one of our web servers just WANTED to
    connect via named pipes. Under Windows 2000, we could address this with a
    registry key, but not in Server 2003.



    --
    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. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
    Dave Anderson, Mar 16, 2006
    #12
  13. Bob and Sharon Hiller wrote:
    > Bob,
    > I think I know what may be happening. I believe that you must use a
    > User ID and Password to connect to the DB in ASP,VBScript. I believe
    > this because every connection string that uses a trusted connection
    > works fine in VB 6.0. If I try to use a connection string in VB 6.0
    > that uses User ID and PassWord I get the following error:
    > (Login failed for user '<LoginName>'. Reason: Not associated with a
    > trusted SQL Server connection)


    That's probably because you failed to remove the "Trusted_Connection"
    attribute when attempting to connect with user id and password.
    >
    > I have tried every suggestion from MS website on how to setup a User
    > ID and Password that is associated with a trusted SQL Server
    > connection but cannot make one work. I have SQL 2005 setup in
    > authentication mode = SQL Server and Windows
    >


    Having said that, I am having trouble connecting with integrated security
    from ASP using either MSDASQL or SQLOLEDB. It seems the Windows credentials
    are not being passed correctly. The Event Viewer on my SQL Server has items
    containing:
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT:
    xxx.xx.xx.xxx]

    So even though Anonymous is not on, and Response.Write
    Request.ServerVariables("LOGON_USER") results in my login name being
    displayed, the credentials are not being used to connect to SQL 2005. I will
    do some more investigation later.

    I have no problem using this connection string:

    cn.Open "Provider=sqloledb;Data Source=myserver;" & _
    "Initial Catalog=AdventureWorks;Persist Security Info=False;" & _
    "user id=xxxx;password=xxxx"

    So using SQL Server security is not an option? I can understand why ... it
    is less secure.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 16, 2006
    #13
  14. Not the problem. It must be something in the SQL server setup. I have
    created users with full access rights to the database and admin rights to
    the server and I still get the same error. Once again, this is setup on a
    Win XP system, not a server . The SQL 2005 server is the standard edition. I
    can't even log in to server manager with SQL authorization, only windows.

    Never had these issues with SQL 7 or SQL 2000 running on the same box. I am
    sure that I do not have something set up correctly in 2005. The new and
    improved help system is the worst I have ever seen IMO.

    Bob Hiller

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Bob and Sharon Hiller wrote:
    >> Bob,
    >> I think I know what may be happening. I believe that you must use a
    >> User ID and Password to connect to the DB in ASP,VBScript. I believe
    >> this because every connection string that uses a trusted connection
    >> works fine in VB 6.0. If I try to use a connection string in VB 6.0
    >> that uses User ID and PassWord I get the following error:
    >> (Login failed for user '<LoginName>'. Reason: Not associated with a
    >> trusted SQL Server connection)

    >
    > That's probably because you failed to remove the "Trusted_Connection"
    > attribute when attempting to connect with user id and password.
    >>
    >> I have tried every suggestion from MS website on how to setup a User
    >> ID and Password that is associated with a trusted SQL Server
    >> connection but cannot make one work. I have SQL 2005 setup in
    >> authentication mode = SQL Server and Windows
    >>

    >
    > Having said that, I am having trouble connecting with integrated security
    > from ASP using either MSDASQL or SQLOLEDB. It seems the Windows
    > credentials
    > are not being passed correctly. The Event Viewer on my SQL Server has
    > items
    > containing:
    > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT:
    > xxx.xx.xx.xxx]
    >
    > So even though Anonymous is not on, and Response.Write
    > Request.ServerVariables("LOGON_USER") results in my login name being
    > displayed, the credentials are not being used to connect to SQL 2005. I
    > will
    > do some more investigation later.
    >
    > I have no problem using this connection string:
    >
    > cn.Open "Provider=sqloledb;Data Source=myserver;" & _
    > "Initial Catalog=AdventureWorks;Persist Security Info=False;" & _
    > "user id=xxxx;password=xxxx"
    >
    > So using SQL Server security is not an option? I can understand why ... it
    > is less secure.
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Bob and Sharon Hiller, Mar 16, 2006
    #14
  15. Bob and Sharon Hiller wrote:
    > Not the problem. It must be something in the SQL server setup. I have
    > created users with full access rights to the database and admin


    Using SQL Logins or existing Windows accounts?

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 16, 2006
    #15
  16. Bob,
    I set up an account for SQL Login in with admin rights. When I start up
    Management Studio I cannot log in as a SQL Server user. I always get the
    rights error. If I log in under windows it works fine. Maybe I just don't
    know how properly set up user rights under the new security rules.

    Bob Hiller

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Bob and Sharon Hiller wrote:
    >> Not the problem. It must be something in the SQL server setup. I have
    >> created users with full access rights to the database and admin

    >
    > Using SQL Logins or existing Windows accounts?
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Bob and Sharon Hiller, Mar 16, 2006
    #16
  17. Bob,
    I just set up the same scenario using SQL server 2000 on a different system.
    Windows XP Home. I went in to Enterprise manager and set the server to SQL
    and Windows Authentication. It immediately asked me for a password for the
    sa account. I entered one and then tested with VB 6.0 and everything works
    fine. I tried a few different connection strings and they all worked. Each
    time I tried to access the DB it asked for my password just as expected. No
    of this seems to happen in SQL 2005. the sa account does not seem to work at
    all.

    Bob Hiller

    "Bob and Sharon Hiller" <> wrote in message
    news:...
    > Bob,
    > I set up an account for SQL Login in with admin rights. When I start up
    > Management Studio I cannot log in as a SQL Server user. I always get the
    > rights error. If I log in under windows it works fine. Maybe I just don't
    > know how properly set up user rights under the new security rules.
    >
    > Bob Hiller
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> Bob and Sharon Hiller wrote:
    >>> Not the problem. It must be something in the SQL server setup. I have
    >>> created users with full access rights to the database and admin

    >>
    >> Using SQL Logins or existing Windows accounts?
    >>
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get a
    >> quicker response by posting to the newsgroup.
    >>
    >>

    >
    >
    Bob and Sharon Hiller, Mar 16, 2006
    #17
  18. You need to check the server properties and verify that both SQL Server and
    Windows Authentication mode are enabled on the Security page.
    Open Books Online and paste this into the address bar to see the relevant
    Help topic (you can also paste it into the IE browser address field):
    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-
    234f1ebfe622.htm


    Bob and Sharon Hiller wrote:
    > Bob,
    > I set up an account for SQL Login in with admin rights. When I start
    > up Management Studio I cannot log in as a SQL Server user. I always
    > get the rights error. If I log in under windows it works fine. Maybe
    > I just don't know how properly set up user rights under the new
    > security rules.
    >
    > Bob Hiller
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> Bob and Sharon Hiller wrote:
    >>> Not the problem. It must be something in the SQL server setup. I
    >>> have created users with full access rights to the database and admin

    >>
    >> Using SQL Logins or existing Windows accounts?
    >>
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 16, 2006
    #18
  19. Bob and Sharon Hiller wrote:
    > Bob,
    > I just set up the same scenario using SQL server 2000 on a different
    > system. Windows XP Home. I went in to Enterprise manager and set the
    > server to SQL and Windows Authentication. It immediately asked me for
    > a password for the sa account. I entered one and then tested with VB
    > 6.0 and everything works fine. I tried a few different connection
    > strings and they all worked. Each time I tried to access the DB it
    > asked for my password just as expected. No of this seems to happen in
    > SQL 2005. the sa account does not seem to work at all.
    >

    See the Help topic I just posted a couple min. ago. The SA account is not
    automatically enabled when switching to SQL Server security mode.

    You really don't want to be using the SA account in your application
    anyways. Guard that account. Somebody could do quite a bit of damage using
    that account, not only to your SQL Server, but also to the machine on which
    SQL is running, not to mention your network. SA has many more privileges
    than will ever be needed by an application. The best practice is to create
    a "least-privileges" account, i.e., and account with the fewest privileges
    needed to accomplish the tasks performed by the app.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Mar 16, 2006
    #19
  20. Re: ASP Access to SQL SERVER change IT IS WORKING!!!

    Thanks to all who contributed. After a week of struggle I am finally where I
    needed to be 2 weeks ago. At least I finally have a smile on my face.

    It is finally working in VB and ASP. Here is what I did. I created SQL login
    accounts and re-booted system. They did not work. the sa account did not
    even work. I then changed Authentication back to Windows Only(I did not
    change any account information). Re-booted again. Changed back to SQL Server
    and Windows Authentication and re-booted again.
    All accounts are now working as expected.

    Hopefully, my final question.
    If I want to set up a group of users who can look at one Database and 8 of
    the 16 available tables in that database what permissions should I set?


    Sincerely,
    Bob Hiller


    "Bob and Sharon Hiller" <> wrote in message
    news:...
    >I have an ASP page that was done in VBScript
    > It is setup to read an Access database and I need to change it to
    > read
    > a Sql 2005 Database.
    >
    > The code that is used to open the Access Database:
    >
    > Set adoConnection = server.CreateObject("ADODB.Connection")
    > Set adoRecordset = server.CreateObject("ADODB.Recordset")
    > adoConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
    > Dim strLocation, iLength
    > strLocation = Request.ServerVariables("PATH_TRANSLATED")
    > iLength = Len(strLocation)
    > iLength = iLength - 11
    > strLocation = Left(strLocation, iLength)
    > strLocation = strLocation & "../Database.mdb"
    > adoConnection.Open ("Data Source=" & strLocation)
    > adoRecordset.ActiveConnection = AdoConnection
    >
    >
    > In my VB 6.0 app I use the following to open the SQL Database"
    >
    > Set DataBaseTS_1 = New ADODB.Connection
    > DataBaseTS_1.ConnectionString ="Provider=MSDASQL.1;Persist Security
    > Info=False;Extended
    > Properties=Description=Large Pump Data Source;DRIVER=SQL
    > Server;SERVER=LPDATASYSTEM\PL3LP;APP=Microsoft Data Access
    >
    > Components;WSID=LPDATASYSTEM;DATABASE=LargePump;Trusted_Connection=Yes;Initi
    > al Catalog=LargePump"
    >
    > DataBaseTS_1.Open
    >
    > How can I get the VBScript to open the SQL Database?
    >
    > Thanks,
    > Bob Hiller
    > Lifts for the Disabled LLC
    >
    >
    >
    >
    Bob and Sharon Hiller, Mar 16, 2006
    #20
    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. Vaap
    Replies:
    2
    Views:
    1,144
  2. =?Utf-8?B?UmV6YQ==?=
    Replies:
    3
    Views:
    17,890
    Carlos Barini
    Jun 7, 2004
  3. Daves
    Replies:
    1
    Views:
    362
  4. cooldv
    Replies:
    3
    Views:
    261
    WIlliam Morris
    Oct 6, 2003
  5. Belinda
    Replies:
    4
    Views:
    346
    Bob Barrows [MVP]
    Jun 11, 2004
Loading...

Share This Page