none
PLEASE? Hosting service says DSN connection is fine but...

    Question

  • Hi all... I have a website running for 9 years with few issues using MSaccess.  This fall we upgraded to sql server.  Hosting service says they are running SQL SErver 2005.

    Once the DB was built and we adjusted code for syntax differences, we thought we were golden.  Created a new DSN on the hosting server and switched our code to use that.  MANY issues after that but we finally had something we thought was running.  Today, after some new issues we are no longer able to connect to the DB.  After discussing the issue with the hosting service all day long *sigh* I am stuck.

    Here's what i can do:

        - I can connect to the DB remotely via MS SQLServer express using the DB server URL and a user password.

        - I can connect to the DB using a DSN-less connection but i don't wasn to expose my password. We are using old technology plain asp not .net (someday..)

    I can not connect using The DSN.

    I have a test page that i took from a msn site.  the code is pretty strightforward...


    Set con = Server.CreateObject("ADODB.Connection")
    con.Open "DSN=MYDSN;Trusted_Connection=yes;"

     strSQL = "SELECT * FROM hit_count WHERE real_Page ='PVJSL Home'"
     
     SET rsCounter = con.execute(strSQL)
     
     response.write rsCounter.Fields("Real_page").Value
     
    rsCounter.Close
    con.Close
    set con = Nothing

    the error i get is this: 

    Microsoft OLE DB Provider for ODBC Drivers

    error '80040e4d'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'HOSTING\iusr_2169706660'.

    /testpage.asp, line 9

    Prior to adding the "trusted_Connection" parameter i was getting the same error with no user info... it was blank.

    It was suggested that i replace the server name in the DSN with the sql server IP and that has made no difference.

    The hosting site claims it works fine from their site.  My questions:

    1.) is there sometool that i can use to test the dsn from my pc other than via the website.

    2.) Can anyone provide me with code or a link to a site with examples that i can try? I HAVE been to connectionstrings.com and found nothing there that has helped.

    THNAK YOU THANK YOU THANK YOU for any help!!


    • Edited by notUnique Saturday, April 14, 2012 11:54 PM
    Saturday, April 14, 2012 11:20 PM

All replies

  • Just an update...  I finally broke down and went with a dsn-less connection but i am somewhat concerned with having the DB password in the code.  any way to encrypt that?

    I'll google it too but it would still be nice to know how i can better trouble shoot the dsn on the host server.  In other words, i want to prove to them that they screwed up, if they did, or prove that i screwed up by not doing something i should have.

    Thanks!

    Sunday, April 15, 2012 1:45 AM
  • [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'HOSTING\iusr_2169706660'.

    /testpage.asp, line 9

    Prior to adding the "trusted_Connection"

    Hello,

    A DSN don't save passwords, you have to pass user id and password within connections string.

    With Trusted_Connection=true you app connects with the service account of the web service, not with a may specified SQL account. You could grant access and read (+write) permissions for that account to get it working.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Sunday, April 15, 2012 6:48 AM
  • I was under the impression that the user ID used in the DSN was a trusted ID and the server would validate that ID and not need a password.  I am wrong?

    Seems no advantage to using a dsn then.

    Sunday, April 15, 2012 12:59 PM
  • hi, check out the below given link, may help you.

    http://support.godaddy.com/help/article/36

    Tuesday, April 17, 2012 11:51 AM