ASP Access to SQL SERVER change

[Bob and Sharon Hiller]

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

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.

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

http://www.aspfaq.com/show.asp?id=2126

 

[Bob and Sharon Hiller]

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

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?

 

[Bob and Sharon Hiller]

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

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.

 

[Mike Brind]

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

 

[Anthony Jones]

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

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.

 

[Bob Barrows [MVP]]

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

 

[Bob and Sharon Hiller]

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

Nobody can help you if you just throw up your hands instead of describing
your symptoms.

 

[Bob and Sharon Hiller]

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

 

[Dave Anderson]

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

 

[Bob Barrows [MVP]]

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

 

[Bob and Sharon Hiller]

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

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?

 

[Bob and Sharon Hiller]

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 and Sharon Hiller]

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

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

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.

 

[Bob and Sharon Hiller]

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