Cannot Connect Application to MS SQL 2005

I have been trying to set up a new DiscountASP account using MS SQL 2005. I simply cannot manage to connect from my application using OLE with a connection string. I have tried many variations on the connection string, based on what is in the knowledgebase and various posts in this forum, but none of them will work. Here are variations that I have tried (with the basic code):

'Create an ADO Connection
Set cn = Server.CreateObject("ADODB.Connection")

' Specify the OLE DB provider.
cn.Provider = "sqloledb"

' Specify connection string on Open method.
ProvStr = "Data Source=tcp:[servernum].discountasp.net;Initial Catalog=[mydbname];User ID=[mydbname]_user;Password=[mypwd]"
' ProvStr = "Provider=SQLOLEDB;Data Source=tcp:[servernum].discountasp.net;database=[mydbname];uid=[mydbname]_user;pwd=[mypwd]"
' ProvStr = "Provider=sqloledb;Data Source=tcp:[servernum].discountasp.net;Initial Catalog=[mydbname];User Id=[mydbname]_user;Password=[mypwd];"
' ProvStr = "Server=[servernum].discountasp.net;Database=[mydbname];UID=[mydbname]_user;PWD=[mypwd];"
' ProvStr = "Data Source=[servernum].discountasp.net;Initial Catalog=[mydbname];User ID=[mydbname]_user;Password=[mypwd]"
cn.Open ProvStr

I've tried all of the variations in the code above (all of which are commented out, except one, of course) - mostly I just get:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user '[mydbname]_user'.

I am able to login from my local network through SQL Server Management Studio using the exact same values I am putting in the connection string - i.e. I put in to Management Studio:
Server Type: Database Engine
Server Name: [servernum].discountasp.net
Authentication: SQL Server Authentication
Login: [mydbname]_user
Password: [mypwd]

and I can get in fine. But when I put the same values into a connection string, using every possible format I can find anywhere, it just won't work. I am able to get the same application working fine on my local network using MS SQL 2005 and specifying a connection string in this way. Does anyone have any idea why I'm just not getting it to work on DiscountASP?

 

Well the format that works on my local network is:

"Server=[servername];Database=[dbname];UID=[userid];PWD=[password];"

That doesn't work at all on DiscountASP - it produces the error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Invalid authorization specification

My company has another DiscountASP account, and that still uses SQL Server 2000. In that account, the format:

"Data Source=[servername];Initial Catalog=[dbname];User ID=[userid];Password=[password]"

works fine. But in the new SQL 2005 account, this format produces the error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user '[userid]'.

This is using the exact same values that work for login via SQL Server Management Studio. I tried adding 'tcp:' before the server name, as some sources recommended that, but it doesn't change the error at all. I'm running out of ideas to try. I've put out a general appeal to others in my company and other coders I know, and will try anything anyone suggests.

 

OK, well at this point I just give in, and will have to abandon this new account at DiscountASP as unuseable, and consider other hosting arrangements. I've tried everything. I created a new SQL Server User through the DiscountASP Control Panel SQL Management options - when I tried to use that to connect, my application crashed on an HTTP 500 error (and this was changing nothing but the userid and password values, and running code that works fine on my local network). So then I tried resetting the database, using the CP SQL Management tools - so that should reset the default user, with my CP password. Well, yes, that works for Management Studio, and for Import/Export - I can get in, re-upload my tables. But when I try and run the application, it's back to 'Login failed'. I've exhausted the possibilities - this account is simply unuseable.

 

I'm still working on this. It seems the issue is that IIS ADO security is handled differently on DiscountASP to on my local network. It's clearly nothing to do with the SQL 2005 server per se, since I can run the application on my local network with the connection pointing to the DiscountASP SQL Server, and the application runs fine, albeit a little slowly, connecting to, reading from and updating the SQL data on the DiscountASP server. But when I transfer the exact same code to the DiscountASP web server, it fails on a login error whenever it tries to use the ADO connection for a recordset.

What I am doing is establishing a DB connection for each user's session in global.asa and then using that wherever database connectivity is required in the app. And that has always worked fine with SQL 2000 both locally and on DiscountASP, and it works fine locally with SQL 2005. But it won't work, at least so far, with SQL 2005 on IIS at DiscountASP. So I'll either have to give in and set up a separate connection in every script that needs the database (which I don't really want to do, but which will, apparently, based on a quick test, work). Or I will have to keep banging my head against the wall trying figure out how to get a connection defined globallly for the user's session working on DiscountASP. I guess reading whatever I can find on ADO security changes in IIS would be the next step.

 

Well, I never did really figure out the cause of the basic problem here - many ways of working with SQL 2005 data via ADODB that work perfectly fine on my local network fail on the DiscountASP IIS server at the point that the ADODB Recordset Activeconnection property is set. A 'login failed for user [assigned_db_user]' error occurs pointing to the line where the Activeconnection property is set. I spent hours researching this, and never came up with an answer as to why this should be or how to get around. I tried many variations on the code, and had no trouble on my local network doing it in a variety of ways... but they all failed on the same error on the DiscountASP web server.
Eventually I have worked around the problem - rather than opening a connection in global.asa and then using that connection as Activeconnection whenever I need a Recordset (as I have done for years in various applications, and can still do without problems, other than at DiscountASP), I am now just setting the connection string in global.asa, and then defining and opening a new connection in every script that needs to do database access, using that connection string. This seems to work ok. I do think it's an inferior method, as it can be tricky to ensure that connections get closed properly, but I don't seem to have any choice, since I can't find any other method that will work at DiscountASP.