none
Excel 2016 Get & Transform SQL "User Was Not Authorized" RRS feed

  • Question

  • I having problems connecting to a 2008 SQL Server DB from Get and Transform in Excel 2016.

      • I can connect with SQL Server Management Studio
      • I can connect from Excel 2016 "Get External Data" from SQL Server
      • I get a "user was not authorized" when connecting to SQL Server from "Get and Transform" New Query from SQL Server DB.

      I'm using a SQL Account (not windows authorization.  For testing I'm using a full system admin SQL account.

      From the above problems it would seem that Get External Data - SQL Server uses different code to Get and Transform - New Query - SQL Server.

      However, Get and Transform works fine against a local SQL server DB on the same PC as Excel.

      The connection with the problem is to:

      • SQL Server 2008 DB
      • Connection string is using tcp-ip address\sqlservername
      • Logon is with a SQl Server account (not windows authentication
      • SQL Server Management Studio 2016 connects to SQl DB with no issues.
      • Get External Data from Excel 2016 connects and extracts data with no problem.
      • Get and Transfor4m NEW QUERY - SQL Server gets "user was not authorized"

      Any ideas?  This has got me baffled.

      Later,

      Savin


    Cheers, Savin Smith

    Tuesday, July 5, 2016 1:32 PM

Answers

  • I May have solved the problem. But unfortunately, now that I've managed to get a connection, I can't get the screen to appear again. Once I reboot an everything I will try and get a full description. But this may help others:

    • Get and Transform ALWAYS tries to connect using your windows credentials first.  If it succeeds it does not even give you the option to use SQL credentials.
    • If it can't connect, then it gives you the chance to use alternate credentials.  BY DEFAULT the screen is looking for alternate Windows Credentials.
    • HOWEVER, a new tab has appeared on the left navigation panel. It says Database. 
    • Click on the database and it now allows you to put in a SQL Account and password to connect.
    • If you successfully connect it stores this info somewhere and does not bother to ask you again.  WHICH IS WHY I can't reproduce this at the moment LOL!

    Its easy to miss the Database tab in the left hand navigation because the Get External Data connection screens do not work the same way.

    Hope this helps someone else.


    Cheers, Savin Smith

    Tuesday, July 5, 2016 6:15 PM

All replies

  • I May have solved the problem. But unfortunately, now that I've managed to get a connection, I can't get the screen to appear again. Once I reboot an everything I will try and get a full description. But this may help others:

    • Get and Transform ALWAYS tries to connect using your windows credentials first.  If it succeeds it does not even give you the option to use SQL credentials.
    • If it can't connect, then it gives you the chance to use alternate credentials.  BY DEFAULT the screen is looking for alternate Windows Credentials.
    • HOWEVER, a new tab has appeared on the left navigation panel. It says Database. 
    • Click on the database and it now allows you to put in a SQL Account and password to connect.
    • If you successfully connect it stores this info somewhere and does not bother to ask you again.  WHICH IS WHY I can't reproduce this at the moment LOL!

    Its easy to miss the Database tab in the left hand navigation because the Get External Data connection screens do not work the same way.

    Hope this helps someone else.


    Cheers, Savin Smith

    Tuesday, July 5, 2016 6:15 PM
  • How did you get it to work, I'm facing the same challenge in 2020

    "The User was not authorized"

    Wednesday, August 26, 2020 12:17 PM