Moving frrom Access 2000 to SQL Server 2000

[DonP]

I'm trying to move our database from MS Access2000 to SQL Server2000. The database was easy to import and the system DSNs easy to create. I installed SQL using Windows Authentication, which supposedly does not require any parameters to use the database, yet I get an error that the login failed. I tried the string both with my user name/password and with nothing at all but either gives the same results. Did I miss something? Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DonP]

I forgot to say that I am trying to access the SQL 2000 database from VBscript using a connection string something like this:

[tt]"dsn=DATABASE_SQL;uid=UserID;pwd=UserPW;"[/tt]

with the real username and password and this is where I am having the problem. No matter what I try (my own account or Administrator account or whatever - mine is in the Administrator's group too), I get the error that the Login failed. Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[tlbroadbent]

What does the complete error message say? Does it indicate why the connection failed?

Is the DSN setup to use a trusted connection? This is required to use NT Authentication.

Is the VBScript in an ASP page? If so are you running the ASP page on a web server? There are some limitations you need to take into account with NT authentication for ASP pages on a Web Server.

Perhaps, the following articles will help.

INF: How to Access SQL Server Within Active Server Pages

http://support.microsoft.com/support/kb/articles/Q169/3/77.ASP

INFO: Accessing SQL Server with Integrated Security from ASP

http://support.microsoft.com/support/kb/articles/Q176/3/77.ASP

HOWTO: Use ASP with a SQL Trusted Connection with Guest Account

http://support.microsoft.com/support/kb/articles/Q176/3/80.ASP

Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[DonP]

Yes, it is an ASP page on a Web server though as I am getting the scripts changed over for SQL, it is running under IIS on my workstation rather than being on the internet. Basically, I am logged onto the PC with my own account which has full administration rights. I don't have the exact message because I'm not in the office so I will have to post it tomorrow. I'll also check the links then. Thanks! Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DonP]

The exact error message is:

[tt][Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'

Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'DON\IUSR_DON'.[/tt]

Strangely, I can test the database connection in UltraDev, which pulls up the records just fine. It is the same code that when run (via IIS) in the browser, that does not work.

Now I'll check out the links you provided to see if any of them shed any light on the problem. Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DonP]

I finally got my test script to access the SQL 2000 database by:

[tt]rs.ActiveConnection = "dsn=DATABASE;UID=sa;PWD=;"[/tt]

which is something I had already tried many times before. This will probably be OK, but still, I would like to know why I can't logon to it with my own user name and password though, nor with Administrator. Either should work. Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[Guest_imported]

YOu need to make sure the user that you are trying to connect to SQL server with is a SQL Server user and not an NT user. If you want an NT user to connect you'll need to do authentication through IIS and then just leave the uid/pwd information blank in the connection string.

 

[HUSS]

i am trying to create a SQL-SERVER2000 database mit ACCESS 2000 front end. i am not sure how to start! do i create the database in access and then import it to sql-server or direct in sql sql-server? and how to create the access masks to manipulate data in sql-server?
please help!!
thanks a lot

 

[BayouTech]

I have an Access 97 database that I upsized the back-end to SQL 7.0
What I need to know now is once I make the links to the back-end, how do I program in Access VBA to connect to the back-end so the username and password gets sent to SQL without user input every time a user opens the front-end. I have tried using a connection string and a workspace object but it doen't work. I need help ASAP as a client is waiting for this to be put in production. Thanks!
Kind Regards
BayouTech