Post reply

SQL server does not exist or access denied: not a straight forward

  • November 4, 2005 at 10:27 am

    #102906

    Looks like a straight forward issue: but it is not. All of a sudden this stopped working from this AM...

    I connect to SQLSER using classis ASP: my connect string is:

    cnn="Provider=SQLOLEDB;Data Source=Server\Instance;Initial Catalog=db;UID=sqllogin;Password=pwd"

    The instance and db is up and running.

    I can connect to this db/userid/password via sql query analyzer.

    I can create dns from the web servers to the SQLSERVER\instance.

    I don't know what is wrong. windows event log shows nothing about this. SQL log is 1 GB and cannot open.

    The SQLSERVER and web server are on different  servers. All are windown 2003.

    Please help !!!

    thanks,

    -c

     

  • November 4, 2005 at 10:56 am

    #602407

    Did anything else change?

    Network/firewall changes?

    That error is generic, but indicates only a couple things.

    username wrong, but you checked that.

    Sql server authentication mode windows only, or sql and windows, but you checked that too.

    No connectivity between the two servers. Have you logged on to the web server, and tried to connect using a sql type tool?

     

  • November 4, 2005 at 10:58 am

    #602408

    Nothing as changed.

    I can create a dsn from the web server to the sqlserver, connect --> works !!

    I am running out of options.

  • November 4, 2005 at 12:08 pm

    #602425

    The following work around works for now, in case anybody is interested.

    cnn="Driver={SQL Server}; Server=IPaddress;Address=serverIP,portNumber;Network=DBMSSOCN;Database=mydb;Uid=sqllogin;Pwd=pwd"

    I am not sure why SERVER\Instance name stopped working: the networking people say that nothing changed.

    -ch

  • November 4, 2005 at 2:01 pm

    #602473

    I don't know the solution to your issue, but for the error log do this:

    Run

    EXEC SP_Cycle_Errorlog

    Then find where the error logs are stored on your server. Usually on the C drive in the SQL Server folders. You should find a folder called Logs.

    There may be more than one. The one without a number is the current one. Open the one that is prior to the current one (usually errorlog1). Open it in Notepad.

    TIP: I have a job that runs sp_cycle_errorlog every Friday so my files don't get too big to read.

    -SQLBill

  • November 4, 2005 at 2:06 pm

    #602474

    (quote) not sure why SERVER\Instance name stopped working (end quote)

    This usually indicates that the DNS server is not resolving names. Maybe you are going through a different DNS server than before.

    One possible way to check: use PING. PING the server with the name and see if it fails. Then PING it with the IP address. If only the IP address works, then it's a name resolution problem with the DNS server. You could have been using a DNS server that did name resolution in the AM and now be going to one that doesn't use name resolution.

    -SQLBill

  • November 8, 2005 at 10:32 am

    #602834

    I am having a similat issue on some of our servers here and the workaround is to put in a registry key, or modify an existing one.  Not sure if this applies to you or not and I'm not completely satisfied with it, but if you look in the registry at:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect

    You'll see where the client machine is getting connection info for SQL Server.  The client machine will look here first for connection info, something along the lines of:

    -150536184:tcp:SCDB, 1354

    So a hash of the SQL version (I think) followed by the protocol used to connect, followed by the servername, port number.

    If there's no value in the registry then the client machine should get a value back from the server as to which port is being used, use that port and write the registry value back out, this is the part that is failing for me and I haven't fixed.  My current workaround is the have that key written out in a login script so it's there.

    One note, if the users try to connect and have that Registry key, but cannot connect then the key is deleted (this is how dynamic ports get updated, then the new port number is read and written back to the registry). 

    Does anyone know details of the process where a client machine reads the port number for a non default instance of SQL?

    -Chris Mackin

  • November 9, 2005 at 11:47 pm

    #603164

    I got the same problem,because provider name is not correct.

    so please give the correct provider name and check.

     

    Regards

    Balasubramanian

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply