Re: Here's a rather broad question...

I'm familiar enough with using ADO with VBScript/Javascript in ASP pages,
having developed this particular system (and used it for several years
now)
on XP 32-bit / SQL Server 2000 (and then migrated to SQL Server 2005 last
year shortly after it came out). I'm now at a stage where I use XP 64-bit
as my primary OS, so it's only natural that I'd want to migrate this to
the
64-bit version of SQL Server 2005.

I agree with the naturalness, thats my thoughts too. It is the way it will
go.
The only question is whether it is premature. It seems to me that not all
the 64-bit support is in place.
Given time, it will be.

The other question here is : Will Microsoft fix problems or keep running
for technology that in their terms is legacy for new platforms? An example
is ADO. It is clear to me that Microsoft are putting all their efforts into
ADO.NET and not its predecessor, will they fix any ADO problems that arise?
It may be the answer is "yes" as they might be willing to keep non-.NET
technology running on 64-bit Windows. MDAC 2.8 was fixes for 64-bit
Windowss.

On XP 32-bit it was pretty straightforward--I set up a system data source
with the ODBC control panel applet, and my ASP code simply used
"DSN=[whatever]" as its connection string--that's it, and I had no
authentication or permission issues with SQL Server 2000. When I made the
move to SQL Server 2005 (but still on XP 32-bit) I remember having to
assign
specific permissions to grant the anonymous IIS user access to the
database.

Right.

The only thing I would say is that Microsoft have frozen development on ODBC
stuff and they have removed distributing the OLEDBODBC provider from MDAC
(you can still download it though).
That provider allowed ADO to connect to databases that only came with an
ODBC driver, efectively it is piggy-backing. ODBC is now 2 legacy
technologies ago (if you count ADO as 1 legacy technology ago).

The problem I'm now seeing seems to be twofold: (a) I'm told by the
system
the data source name doesn't exist despite recreating it with the control
applet and (b) if I try to use a "real" connection string right in the ASP
code (trying the various examples provided at www.connectionstrings.com)
I'm
running into authentication problems just trying to connect. I suspect my
problem stems more from my lack of SQL Server 2005 user/permission
knowledge
than ADO itself. As far as I can tell, I suspect the default (anonymous)
IIS user doesn't have the proper permissions to access the database, and I
just can't figure out the right permissions. The changes I need to make
can
very well be in SQL or IIS (or both), but I figured since ADO is where the
link between the two is made, it would make sense to ask in an ADO
(*classic* ADO) newsgroup...

My gut feeling in trying to solve this would be to cut out ODBC and just use
pure OLEDB/ADO. For the SQL Server provider, there are 2 authentication
methods: SQL Server (UserID/Password required) or Windows NT
Authentification (No UserID/Password required). I have managed to get both
going in the past with one of our applications and ADO. With NT
Authentication the connection string has
";Integrated Security=SSPI" appended.
I don't know if that can be done with IIS

My general connection string is

"Provider=sqloledb;Data Source=yourserver;Initial Catalogue=yourdatabase"

with UserID & Password passed in

pConnection->Open(bstrConnection, bstrUserID, bstrPassword, 0L);

with bstrUserID, bstrPassword set to "" if the clause ";Integrated
Security=SSPI" is appended

Any good?

Stephen Howe


.

Relevant Pages

  • Re: Connection from ASP- ODBC vs. SQLOLEDB
    ... The ASP code is already using ADO (adodb.connection, ... The new provider is better than ODBC isn't it, ... SQL Server Client Network Utility- "Enable Shared Memory": ... ASP, ASP.NET as well as Microsoft Content Management Server 2002 SP1a. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server extremely slow
    ... terms of what is meant by a dis-connected ado recordset. ... table in a mdb file could be considered disconnected from the server ... Well, ok, but keep in mind the disk drive is on sql server! ... 10 reocrds from the server via odbc does not produce more ...
    (comp.databases.ms-access)
  • Re: SQL Server extremely slow
    ... my personal experience with a similar setup as your (Sql Server ODBC ... My fix was to use ADO ... With ODBC the connection remains open the ...
    (comp.databases.ms-access)
  • Re: SQL Server extremely slow
    ... my personal experience with a similar setup as your (Sql Server ODBC ... against the sql server (ADO was specifically designed for use with sql ... the connection is closed. ...
    (comp.databases.ms-access)
  • Re: [Access2003, VBA] Use DAO or ADO?
    ... ODBC, whatever the back end. ... to a jet backend to open a ado recordset. ... customers want/need sql server and another wants to use MySql. ...
    (comp.databases.ms-access)
Loading