|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ASP Access to SQL SERVER change
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 wrote:
> I have an ASP page that was done in VBScript <snip>> 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> -- 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. 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. Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:usPl40ESGHA.5108@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 and Sharon Hiller wrote:
> Here is what I have tried: No records are returned(This table has Nothing to do with your problem, but you should avoid odbc. See the link in> 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" my original reply. Show quote > adoConnection.Open I see no attempt to check the recordset's EOF property. How are you> > 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. > 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,
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 Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:ue3OW%23GSGHA.5728@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. > > Bob and Sharon Hiller wrote:
Show quote > Bob, GetRows returns an array, not a numeric value.> 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 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
Show quote
"Mike Brind" <paxton***@hotmail.com> wrote in message Problem is GetRows will error if there are no rows. Use:news:1142514010.162680.21430@u72g2000cwu.googlegroups.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 > 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 and Sharon Hiller wrote:
> Bob, With a default server-side, forward-only cursor, Recordcount will always> After > intRecordCount = adoRecordset.RecordCount > 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. > Mike addressed this one.> I also tried: > intRowsCount = adoRecordset.GetRows > Why? Where does that article say anything about reconfiguring SQL Server if> Looking at the link you sent, it appears that I would need to totally > reconfigure the SQL server to use that method. 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") adoConnection.ConnectionString = "Provider=SQLOLEDB;" _>>> 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" >> & "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. 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 Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:ObyyS0PSGHA.224@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. > > Nobody can help you if you just throw up your hands instead of describing
your symptoms. Bob and Sharon Hiller wrote: Show quote > 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]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:ObyyS0PSGHA.224@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,
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 Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:%236L%2323QSGHA.4384@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]" <reb01501@NOyahoo.SPAMcom> wrote in message >> news:ObyyS0PSGHA.224@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 wrote:
> (Login failed for user '<LoginName>'. Reason: Not associated with a Check out http://www.connectionstrings.com/> 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 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 and Sharon Hiller wrote:
> Bob, That's probably because you failed to remove the "Trusted_Connection"> 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) attribute when attempting to connect with user id and password. > Having said that, I am having trouble connecting with integrated security> 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 > 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. 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 Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:%237asOmRSGHA.1236@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 and Sharon Hiller wrote:
> Not the problem. It must be something in the SQL server setup. I have Using SQL Logins or existing Windows accounts?> created users with full access rights to the database and admin -- 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,
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 Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:emXvljSSGHA.4440@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,
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 Show quote "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message news:uINMBpTSGHA.5500@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]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:emXvljSSGHA.4440@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 wrote:
> Bob, See the Help topic I just posted a couple min. ago. The SA account is not> 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. > 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. 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: Show quote > 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]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:emXvljSSGHA.4440@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. 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 Show quote "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message news:uZB8lyESGHA.2300@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;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 > > > >
Show quote
"Bob and Sharon Hiller" <aokl***@tir.com> wrote in message Please don't top post. This should also be in a new thread and in the db news:u6ZYpIVSGHA.2224@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? 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 and Sharon Hiller wrote:
> Thanks to all who contributed. After a week of struggle I am finally Great!> where I needed to be 2 weeks ago. At least I finally have a smile on > my face. > So you did not have to run the ALTER LOGIN command as shown in the Help> 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. topic I pointed you to? Interesting ... > You are now getting out of the realm of ASP and into the realm of SQL Server> 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? > 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,
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 Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:e75SLkcSGHA.4168@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. > >  |
|||||||||||||||||||||||
Other interesting topics