none
error connecting to server database RRS feed

  • Question

  • I opened a content issue about being unable to connect to a server database at https://github.com/microsoft/TechnicalCommunityContent/issues/28.  (Since I'm unsure whether the repository is actively maintained, I'm posting this question just in case it's a client error. Thanks if you can answer!)  When I try to import data from the Azure SQL Data Warehouse, it shows the error:

    "Microsoft SQL: cannot open server 'powerbilabs' requested by the login. Client with IP address '76.86.23.154' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range."

    Saturday, August 17, 2019 1:54 AM

Answers

  • If the login you are using has sufficient privileges (db_owner role in master) or you are using the SQL Admin account. Otherwise you will need to have the admin add this to the instance you are attempting to connect with.

    ~Mike

    • Marked as answer by rootsmusic Wednesday, August 21, 2019 6:07 PM
    Wednesday, August 21, 2019 6:00 PM
    Moderator

All replies

  • Hi Rootsmusic,

    This is an IP Allow List issue in that you need to add the Public IP address identified to the firewall. Please see: Azure SQL Database and SQL Data Warehouse IP firewall rules (link). 

    If this is your client Public IP then the easiest way to do this, if you have SSMS installed, is to connect to the Azure SQL Server and authorize the pop-up to add this IP. The document I provided list all the ways you can add IP addresses. Once the IP is added the client application will be able to connect.

    Please let us know if you have additional questions.

    Regards,

    Mike

    Monday, August 19, 2019 10:58 PM
    Moderator
  • If this is your client Public IP then the easiest way to do this, if you have SSMS installed, is to connect to the Azure SQL Server and authorize the pop-up to add this IP.

    Thanks Mike, I can install SSMS.  Since I have never used SSMS, which section should I follow in the help article at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure?
    Monday, August 19, 2019 11:15 PM
  • I provided you instructions on adding IP Addresses. The following is the end-to-end process of using SSMS to connect to your database: 

    Use SQL Server Management Studio to connect and query an Azure SQL database (link)

    Please let me know if you have additional questions.

    Regards,

    Mike

    Tuesday, August 20, 2019 2:06 AM
    Moderator
  • The following is the end-to-end process of using SSMS to connect to your database:

    Mike, I'm the client that's trying to connect to an Azure SQL Database.  Although I don't manage the server's master database, I can still use SSMS to create a firewall rule for my client IP?  Thanks.
    Wednesday, August 21, 2019 2:19 PM
  • If the login you are using has sufficient privileges (db_owner role in master) or you are using the SQL Admin account. Otherwise you will need to have the admin add this to the instance you are attempting to connect with.

    ~Mike

    • Marked as answer by rootsmusic Wednesday, August 21, 2019 6:07 PM
    Wednesday, August 21, 2019 6:00 PM
    Moderator
  • Otherwise you will need to have the admin add this to the instance you are attempting to connect with.

    Mike: Since I'm not the db_owner or admin, I asked the question at https://social.msdn.microsoft.com/Forums/en-US/f6b8a70c-9404-4145-aefa-bf6c375e754e/server-dba
    Wednesday, August 21, 2019 6:06 PM