locked
Using a contained database login to connect to SQL Server via Excel RRS feed

  • Question

  • I have a SQL Server 2014 database that is partially contained.  I am trying to connect to the database via Excel using a contained user login.  When I choose Data - Other Sources - From SQL Server, I get the Data Connection Wizard window to Connect to the Server. This window allows me to enter the server name and a User Name and Password (for SQL authentication), however it does not allow me to specify a database.  When I click the Next button, I get a 'login failed' error which I assume is happening because the contained user is not a SQL login.

    I have the same issue trying to create an ODBC connection using the ODBC Data Source Administrator. 

    Is there another way for me to connect to a SQL Server database using a contained login with Excel? 

    Wednesday, July 29, 2015 1:53 PM

Answers

  • Try this:
    1. In Excel, on the Data menu, click From Other Sources.
    2. Select From Data Connection Wizard.
    3. Select Other/Advanced, and then click Next.
    4. On the Provider tab, select SQL Server Native Client <number>, and then click Next.
    5. On the Connection tab, complete the server name, user and password, and in the Select the database box, put in the database name.

    This has worked for me with a contained database login. I'm using 2010 at the moment with SQL Server 2016 CTP2.2.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, July 29, 2015 3:45 PM
  • Try to track the connection with sp_who2 from SSMS to see who you are actually logged in as. I tried to repro your problem, but no matter how I tried, I got logged in with Windows authentication and since I'm sysadmin I saw everything. But this is about the first time that I've tried to connecting to SQL Server from Excel.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 29, 2015 9:39 PM
  • Rick Byham, Microsoft's solution should work. I have a feeling that somewhere in the steps was done differently, or you did not saved the connection, so next time it used the default. I am not sure why it did not work :-(


    * regarding the Patti's question, since you see the correct database and only this (and master and tempdb) I assume that you are using the contain user, but he has not got the db_datareader rule or he has some DNY rule.

    1. try to use the SSMS with the contain user to make sure that the issue is related to EXCEL connection and not the user's permissions.

    2. Try to check the user permissions on the table:

    use ContainDB
    GO
    
    -- check the user permissions on the table T1. He should get SELECT
    EXECUTE AS USER = 'ContainUser';
    SELECT * FROM fn_my_permissions('dbo.T1', 'OBJECT')
    REVERT;
    GO

    Please inform us what you get :-)

    * I can add screenshot of what work for me (which is actually what Rick Byham, Microsoft wrote, as much as I remember)... I need some time to collect the images... It can take some time :-) Is this can help?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    Thursday, July 30, 2015 12:22 PM

All replies

  • Try this:
    1. In Excel, on the Data menu, click From Other Sources.
    2. Select From Data Connection Wizard.
    3. Select Other/Advanced, and then click Next.
    4. On the Provider tab, select SQL Server Native Client <number>, and then click Next.
    5. On the Connection tab, complete the server name, user and password, and in the Select the database box, put in the database name.

    This has worked for me with a contained database login. I'm using 2010 at the moment with SQL Server 2016 CTP2.2.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, July 29, 2015 3:45 PM
  • Oops. I meant with at contained database user, not login.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, July 29, 2015 3:46 PM
  • Thank you for your help.  I tried out the Other/Advanced option. 

    On the Data Link Properties window, I filled out the Connection tab information and included the database name.  I clicked the 'Test Connection' button and that succeeded. 

    I then clicked OK which brought up the SQL Server Login window.  I entered the contained user and password and clicked OK.  This brought up the Database Connection Wizard window to 'Select Database and Table'.  The databases listed in the dropdown included the contained database I specified as well as master and tempdb.  However, in the place where it should show the tables in the database, no tables were listed.  The contained user has membership in the dbdatareader role. 

    Any ideas?

    Wednesday, July 29, 2015 5:44 PM
  • Try to track the connection with sp_who2 from SSMS to see who you are actually logged in as. I tried to repro your problem, but no matter how I tried, I got logged in with Windows authentication and since I'm sysadmin I saw everything. But this is about the first time that I've tried to connecting to SQL Server from Excel.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 29, 2015 9:39 PM
  • Rick Byham, Microsoft's solution should work. I have a feeling that somewhere in the steps was done differently, or you did not saved the connection, so next time it used the default. I am not sure why it did not work :-(


    * regarding the Patti's question, since you see the correct database and only this (and master and tempdb) I assume that you are using the contain user, but he has not got the db_datareader rule or he has some DNY rule.

    1. try to use the SSMS with the contain user to make sure that the issue is related to EXCEL connection and not the user's permissions.

    2. Try to check the user permissions on the table:

    use ContainDB
    GO
    
    -- check the user permissions on the table T1. He should get SELECT
    EXECUTE AS USER = 'ContainUser';
    SELECT * FROM fn_my_permissions('dbo.T1', 'OBJECT')
    REVERT;
    GO

    Please inform us what you get :-)

    * I can add screenshot of what work for me (which is actually what Rick Byham, Microsoft wrote, as much as I remember)... I need some time to collect the images... It can take some time :-) Is this can help?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    Thursday, July 30, 2015 12:22 PM
  • Rick, thanks for your help.  There was something wrong with the user setup, although I am not sure what it was.  With this user, it would get the error "The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. " when trying to access anything in the sys schema.

    After I dropped and recreated the contained user, that user could log in and see the tables. 

    Friday, July 31, 2015 2:04 PM
  • Great. Glad to help.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, July 31, 2015 3:42 PM
  • Hi Patti,

    May I comment on 2 small points?

    1. You should close the thread by marking the answer/s, and you can (and should) vote for any useful response, as well. Once you mark one or more responses as answer, the thread will get the icon V instead of ? (and the people that got voted by you or their response was marked as answer, will get some virtual points)

    2. Rick was not the only one that tried to help you. It is nice to see that you remembered to thanks him but what about general thanks or Erland for example. It look bad in my opinion that people try to help and do not get a thanks, even if they did not succeed this time :-) this is like you ignore us :-(

    I am glad that you find the solution :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Saturday, August 1, 2015 3:47 AM