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;Tr usted_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]]

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]

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:us**************@TK2MSFTNGP09.phx.gbl...

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

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]

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:ue****************@tk2msftngp13.phx.gbl...

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.

[Mike Brind]

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

[Anthony Jones]

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...

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.

[Bob Barrows [MVP]]

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]

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ob*************@TK2MSFTNGP10.phx.gbl...

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

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ob*************@TK2MSFTNGP10.phx.gbl...

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]

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2******************@tk2msftngp13.phx.gbl...

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:Ob*************@TK2MSFTNGP10.phx.gbl...

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.

[Dave Anderson]

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.

[Bob Barrows [MVP]]

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]

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

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

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]

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:em**************@TK2MSFTNGP11.phx.gbl...

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]

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" <ao*****@tir.com> wrote in message
news:uI**************@TK2MSFTNGP12.phx.gbl...

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:em**************@TK2MSFTNGP11.phx.gbl...

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

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]" <re******@NOyahoo.SPAMcom> wrote in message
news:em**************@TK2MSFTNGP11.phx.gbl...

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

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 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
"Bob and Sharon Hiller" <ao*****@tir.com> wrote in message
news:uZ**************@TK2MSFTNGP11.phx.gbl...

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;Tr usted_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

[Roland Hall]

"Bob and Sharon Hiller" <ao*****@tir.com> wrote in message
news:u6**************@TK2MSFTNGP10.phx.gbl...
: 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?

Please don't top post. This should also be in a new thread and in the db
group. It would give you more opportunity for others to see you have a
second question.

You may need to rephrase because user's should only have access to what your
application allows them to. If you're referring to developers, you're not
restricted to using one account to access a database. You can set
individual permissions on a table for different users, if that's your goal.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

[Bob Barrows [MVP]]

Bob and Sharon Hiller wrote:

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.
Great!
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.
So you did not have to run the ALTER LOGIN command as shown in the Help
topic I pointed you to? Interesting ...

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?

You are now getting out of the realm of ASP and into the realm of SQL Server
Security. This is a huge topic and you should start in Books Online. I
have not started working with SQL2005 yet so any advice I could give you may
be outdated. However, in a nutshell:
Add the sql logins to the database without assigning them to a global role
(datareader, datawriter, etc.) in that database.
Edit the properties of the specific tables to which you wish to grant the
users access and choose the option/button/page/tab (whatever it is in 2005
Management Studio) to edit Permissions for the object.

You will get more focussed help in the appropriate sqlserver group ... I
think there is a microsoft.public.sqlserver.security group, but you should
check to be sure.

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

Bob,
I could not figure how or where to issue the ALTER LOGIN command. I
switched the Authentication mode a few times and then read in the help file
that you had to have a strong password. I was not sure what that was either.
Another Help search and I changed my login password to a strong one. A soon
as I did that, I got a message box stating that I needed to stop and restart
the SQL Server service before the change would take effect. Instead of
stopping/restarting service I just re-booted. The moral of the story: If you
don't enter a strong password, it does not work. If you do enter a strong
password, re-boot and all is well.

Bob Hiller

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:e7**************@tk2msftngp13.phx.gbl...

Bob and Sharon Hiller wrote:

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.

Great!

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.

So you did not have to run the ALTER LOGIN command as shown in the Help
topic I pointed you to? Interesting ...

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?

You are now getting out of the realm of ASP and into the realm of SQL
Server
Security. This is a huge topic and you should start in Books Online. I
have not started working with SQL2005 yet so any advice I could give you
may
be outdated. However, in a nutshell:
Add the sql logins to the database without assigning them to a global role
(datareader, datawriter, etc.) in that database.
Edit the properties of the specific tables to which you wish to grant the
users access and choose the option/button/page/tab (whatever it is in 2005
Management Studio) to edit Permissions for the object.

You will get more focussed help in the appropriate sqlserver group ... I
think there is a microsoft.public.sqlserver.security group, but you should
check to be sure.

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