locked
Connecting to non-default database in Azure through ODBC RRS feed

  • Question

  • Hello,

    I can successfully connect to Azure through ODBC. I have created a database (or at least, the Transact-SQL to do so didn't throw an exception in my code). Now I am trying to connect to the database I created, and it does not seem to be possible through ODBC.

    According to the documentation:

    "If you do not specify a database in the connection string, you will be connected to the master database."

    Presumably to specify a database in the connection string, I should modify the "default database" field in my ODBC connection. When I configure my ODBC connection, click the checkbox "Change the default database to:", and enter the name of the database I created - chrisTest - I get a message box saying: The database entered is not valid.

    So: How do I connect to a non-default database through ODBC? My SQL Server ODBC is from 2000... do I need to download a more recent one? Where can I do that?

    My ODBC connection tells me the "default database" I enter is not valid. There does not seem to be any other way to specify the database to connect to. Kind of at a loss here. Any help would be appreciated.

    thanks,
    chris
    Tuesday, September 1, 2009 6:27 PM

Answers

  • Hi Chris,
      When you create a DSN, you can check "Change the default database to" and specify the SQL Azure database that you would like to connect. This should help you connect to SQL Azure using a System DSN in ODBC Administrator.

    Thanks,
    Abi
    Wednesday, September 2, 2009 9:57 PM

All replies


  • The connect string for ODBC should look like:

    Driver={SQL Server};Server=tcp:myserver.ctp.database.windows.net;Database=myDB;Uid=mylogin;Pwd=myPassword;

    Notice the Database= can be set to any database you want to connect to.

    You can also get information on the connect string in the "Server Administration" portal -- there is a button for "Connetion Strings"  that will display the different strings for you database.

    You can download the latest drivers from:

        http://www.microsoft.com/downloads/details.aspx?familyid=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en

    Look for "Microsoft SQL Server 2008 Native Client "

    Tony

    Tonyp
    Wednesday, September 2, 2009 8:11 AM
  • Thanks Tony! I'll give it a shot.

    - chris
    Wednesday, September 2, 2009 12:38 PM
  • OK. To clarify, I'm not specifying the ODBC connection string myself. I'm using the "Data Sources (ODBC)" in the Windows Control Panel to configure my ODBC data source.

    Start->Control Panel->Adminstrative Tools->Data Sources (ODBC)

    This is what we use to configure ODBC data sources. This no longer seems to work with SQL Server Azure. I cannot specify the database. Is there a way to get it working with Azure? I know what the connection string is supposed to be. How can I make the Windows ODBC Data Sources tool specify this connection string, by adding the database that I want to add?

    The SQL Server Native Client 10 makes no difference, exact same problem.

    And I have no idea what this Server Adminstrator portal is that you're referring to. There's obviously no such thing in the Control Panel, or in the ODBC dialog.. what I am supposed to be looking for, or at? Is this in an Azure download package? On Windows somewhere? On the interweb?



    Wednesday, September 2, 2009 1:03 PM
  • Hi Chris,
      When you create a DSN, you can check "Change the default database to" and specify the SQL Azure database that you would like to connect. This should help you connect to SQL Azure using a System DSN in ODBC Administrator.

    Thanks,
    Abi
    Wednesday, September 2, 2009 9:57 PM
  • I also get this error message when I enter my SQL Azure database name in the "Change the default database to:" box

    The database entered is not valid

    I was able to change the default database from 'master' by appending database=<mydatabaseName> to the connection string and successfully have the connection use the desired database.
    Thursday, September 3, 2009 9:49 PM
  • Thanks for your reply, I eventually figured it out in code.

    The "Change the default database" does not work with Azure.
    Tuesday, September 8, 2009 7:25 PM
  • I'm trying ODBC connections too, but I'm getting this error:

    ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Windows logins are not supported in the current version of SQL Data Services.
    ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
    ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Windows logins are not supported in the current version of SQL Data Services.
    ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute

    I'm using the connection string shown in the previous post:

    Driver={SQL Server};Server=tcp:yitwxbx6n0v.ctp.database.windows.net;Database=azure;Uid=xxxx;Pwd=xxxx;
    Friday, December 11, 2009 9:51 AM
  • Creating a DSN in Windows ODBC Manager via Control Panel is not going to work unless the driver you choose support some deprecated features. If you are using SQL Server Client 10.0 this is not going to work.

    The main problem is the SQL Azure does not support this with the driver that is on your machine.


    mysorian
    Saturday, December 12, 2009 2:27 PM
  • Hi Chris,

    Can you share what you figured out as maybe there are some people have similar problem like yours?

    Thanks

    Saturday, August 8, 2015 4:16 AM
  • I had to download this update for ODBC and then it worked right away:

    Microsoft® ODBC Driver 11 for SQL Server® - Windows

    https://www.microsoft.com/en-us/download/details.aspx?id=36434

    pem


    Saturday, December 12, 2015 10:29 PM
  • I know this is quite an old post and Azure has changed a lot since then, but I found doing this using PyODBC works.  My connection code looks like this:

    conn = pyodbc.connect(driver='{SQL Server}',
                                      server='tcp:xxx-db.database.windows.net,1433',
                                      database='SomeDatabase',
                                      uid='readOnly@xxx-db', pwd='XyzzY')
    
    

    Tuesday, January 23, 2018 5:09 PM