locked
Desperate | Application gives this error: Database not available RRS feed

  • Question

  • Hello!

    I have a virtual machine, running SQL server 2008 r2 on it.
    On another virtual machine, I run an application that needs a database. I am trying to connect to the database on the other virtual machine but it keeps giving me the error: database not available. 

    Both machines are member of the same domain in the same AD.

    I really tried everything already so I am out of options. Hopefully, you can help!


    This is what I did:
     
    1) Right click on Sql Server in Sql Management Studio (SMS) -> Connections -> Allow remote connections
     
    2) Right click on Sql Server in Sql Management Studio (SMS) -> Security -> Set Auth mode to Win and Sql auth mode
     
    3) Firewalls are definitely disabled.
     
    4) Sql Server Configuration Manager -> Network Configuration -> Protocols -> Named pipes and TCP/IP are enabled.
     
    5) Sql Server browser is enabled and I set the account to the build in account Network Service 
     
     
     
     So I don't know what to do anymore. There are hundreds of posts on the internet but just not the solution for me.

     

    Hopefully you can help!! Thanks in advance!

    Wednesday, November 16, 2011 2:53 PM

Answers

  • What is BackupManager in this context? Is that your application, or some class in it?

    What does your connection string look like?

    If you open a command-line window and run:

    sqlcmd -d thatdatabase

    What happens?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Erjen Rijnders Monday, November 21, 2011 7:54 AM
    Wednesday, November 16, 2011 11:00 PM

All replies

  • Since it is saying that the DB is not available, let's look at the security for that.  What logon are you using? Have you tried a login with sysadmin privileges?  If that works, then you need to look at the privileges for the user login.  Is the login mapped to a user in the target DB?
    Tom
    SQL Server MVP
    Toronto, ON Canada
    Wednesday, November 16, 2011 3:00 PM
  • Hi,

    Also check your SQL Server error logs to see if it's even getting that far.  If you are getting errors, post them back here.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Wednesday, November 16, 2011 3:17 PM
  • Thank you for the very fast reply!

    The login I am using is a domain administrator. I tried another domain user but gives the same error :(

    By the way, the application finds the database server. If I type the hostname of the machine, it says it founds it but still says database not available. I checked if the catalog exists and it does.

     


    Wednesday, November 16, 2011 3:19 PM
  • Are you able to logon locally to the SQL Server?  Is the domain admin a member of the SQL Server sysadmin fixed server role?
    Tom
    SQL Server MVP
    Toronto, ON Canada
    Wednesday, November 16, 2011 3:23 PM
  • also, check whether it's case sensitive, i.e. MyDatabase as opposed to mydatabase... has got me in the past.

    What about those error logs?



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Wednesday, November 16, 2011 3:26 PM
  • I can locally connect to the SQL server. I just made the user a member of the SQL Server sysadmin fixed server role (googled and followed the steps) but still the same problem.

    The event viewer gives a very strange error:

    Problem detected during initialise.

    Detailed information:

    Initialise system failed:

    Initialise failed:

    Initialise database failed:

    database not available.

    at xxxx.AL.BackupManager.Initialise(String& nonFatalMessages)

    at xxx_configurator.Form1.btn_SystemInitialise_Click(Object sender, EventArgs e)

     

    Thats just it and it gives a lot of errors at the same time of these.

    Wednesday, November 16, 2011 3:51 PM
  • I checked if it's case sensitive, maybe it is but I just typed it exactly the way its shows up in the SSMS (sql server management studio).
    Wednesday, November 16, 2011 3:53 PM
  • That is weird.  It doesn't look like a SQL Server error log.

    You mentioned that you can logon locally.  When you added that user to the sysadmn role, did you try logging on locally with that  login?


    Tom
    SQL Server MVP
    Toronto, ON Canada
    Wednesday, November 16, 2011 3:55 PM
  • Yes and that works fine. I am remotely logged in with that useraccount and I am using Windows Authentication during connecting to the SQL server so it gives no errors and I can see everything inside.
    Wednesday, November 16, 2011 4:05 PM
  • I don't know if you want that, but maybe you can login to my server remotely with teamviewer? And watch if everything is how it should be? Maybe weird question but I think I can't solve this myself.

     

    Is that possible?


    maybe I can PM you?
    Wednesday, November 16, 2011 4:14 PM
  • could you try creating a connection to the SQL Server using the ODBC manager on your application server, just to prove connectivity between the 2 boxes, i.e.

    Start > Control Panel > Administrative Tools > Data Sources (ODBC)

    'System DSN' tab

    - Add

    - SQL Server Native Client 10.0  > FINISH

    Then fill in the details to connect to the server...

     

     



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Wednesday, November 16, 2011 4:15 PM
  • Here are the testresults:

     

    Microsoft SQL Server ODBC Driver Version 06.01.7601

    Running connectivity tests...

    Attempting connection
    Connection established
    Verifying option settings
    Disconnecting from server

    TESTS COMPLETED SUCCESSFULLY!

    Wednesday, November 16, 2011 4:20 PM
  • So it seems to be that it has a connection right?
    Wednesday, November 16, 2011 5:47 PM
  • Try creating a SQL Login and using it instead of a Windows Login. Since the error returned is not a SQL Server generated messages, I have a feeling your app is trying to connect using SQL authentication instead of Trusted Connection.

    So check if you get the same error message when using SQL logins too

    Wednesday, November 16, 2011 7:00 PM
  • What is BackupManager in this context? Is that your application, or some class in it?

    What does your connection string look like?

    If you open a command-line window and run:

    sqlcmd -d thatdatabase

    What happens?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Erjen Rijnders Monday, November 21, 2011 7:54 AM
    Wednesday, November 16, 2011 11:00 PM
  • I think the application won't let me use SQL auth. It keeps asking for a domain user.

    And this is the error log of the application so I don't know exactly what that BackupManger means. Its just a part of the application.

     

    When I run sqlcmd -d <catalog name> it gives a new line like this: 1>
    But when I run sqlcmd -d <database name> it says login failed for user DOMAIN\Administrator.

    Strange right? When I try that login with the SSMS just as windows Auth, it works fine

     


    Thursday, November 17, 2011 7:44 AM
  • By the way, I see now that if I expand the catalogname, then security and then users, I don't see the user I am trying to connect with. I see that user only if I expand databases, then security and then logins. Should the same member also be at the 'catalogname' users? Or isn't that neccesary?
    Thursday, November 17, 2011 8:23 AM
  • So this application you are talking about is a third-party program and nothing you have developed yourself?

    You are talking about catalogs and databases. While "catalog" is part of the ANSI terminology, it is not very commonly used with SQL Server.

    This is also strange: I think the application won't let me use SQL auth. It keeps asking for a domain user.

    When you log into SQL Server with Windows authentication, you cannot specify a user name, but you log with the same user that you are logged into in Windows. Possibly, this application of yours impersonate a different Windows user behind the scenes.

    It seems that you are using an application that you don't know too much about. Guess what? We know even less about it. So it will be difficult to help you. But at least if you tell us the name, maybe someone where is familiar with that product.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, November 17, 2011 9:21 AM
  • It is a third-party program indeed. It asks for the catalogname which is just the database I think but usually we installed the application on a local SQL database and that worked always fine so I know pretty much about the application except how it works with Remote SQL. And you're probably right, it is the application I don't understand enough for remote SQL and it's most probably not a setting of the SQL database. So maybe I should start asking questions to the developer of this application.

    Anyway, All of you, thank you very much! I really appreciate all this work and if you need anything from me, just ask :)

    Thursday, November 17, 2011 9:39 AM
  • What is BackupManager in this context? Is that your application, or some class in it?

    What does your connection string look like?

    If you open a command-line window and run:

    sqlcmd -d thatdatabase

    What happens?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    Finally, I found the problem! When you asked me to run the command sqlcmd -d I realize now that I ran that command on the server with the sql database but when I ran it on the application server it gave an error. So all I had to do was install the SQL management tools and the connectivity and than it works fine!!

    Thanks a lot for al your help!

    Monday, November 21, 2011 7:56 AM
  • Finally, I found the problem! When you asked me to run the command sqlcmd -d I realize now that I ran that command on the server with the sql database but when I ran it on the application server it gave an error. So all I had to do was install the SQL management tools and the connectivity and than it works fine!!

    Thanks a lot for al your help!

    Monday, November 21, 2011 7:58 AM
  • Finally, I found the problem! When you asked me to run the command sqlcmd -d I realize now that I ran that command on the server with the sql database but when I ran it on the application server it gave an error. So all I had to do was install the SQL management tools and the connectivity and than it works fine!!

    Glad to hear that it sorted out.

    Although it sounds like an overkill to install the management tools on the server. I think it should be sufficient to install the connectivity and/or the appropriate version of .Net. I would also think that this is a task of the application. Oh, well, another time.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 21, 2011 9:52 PM
  • I had a similar problem.

    I resolved it by adding the sysadmin role to the login I was using.

    This may not be ideal for everyone for security, but was ok for me in my case as I was permitted the permission level


    David Pye

    Wednesday, November 6, 2013 12:47 PM