0

The below mentioned SQL queries we are doing manually using the Query Editor in Azure Portal SQLDB. But, we have to execute these using the PowerShell, and I was went through the couple of articles and unable to find a exact solution to perform this action. If any one have PowerShell script handy, can you guys please.... help!

  1. Login to SQL Database using Admin login.

  2. Execute the following commands.

    CREATE USER [App Service Name] FROM EXTERNAL PROVIDER;
    ALTER ROLE db_datareader ADD MEMBER [App Service Name];
    ALTER ROLE db_datawriter ADD MEMBER [App Service Name];

Amit Verma
  • 2,450
  • 2
  • 8
  • 21
niranjan reddy
  • 46
  • 2
  • Welcome to StackOverflow. Please show us what you have tried yourself. Note this is *not* a script factory. You will need to write the script yourself. If you run into a problem that isn't yet covered by the existing questions and answers, you might post your [mcve] here so that we might be able to help you further. See also: [How to ask a good question](https://stackoverflow.com/help/how-to-ask) – iRon Jul 22 '21 at 12:26
  • Azure SQL Database is almost the same as Microsoft SQL Server, so you can use `Invoke-SqlCmd` from `SqlServer` powershell module – Ivan Ignatiev Jul 22 '21 at 12:32

1 Answers1

0

This is a simple Invoke-SqlCmd call:

Invoke-Sqlcmd -ServerInstance "YourServer" -Database master -Query "CREATE USER [App Service Name] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [App Service Name]; ALTER ROLE db_datawriter ADD MEMBER [App Service Name];"
SE1986
  • 2,534
  • 1
  • 10
  • 29
  • When I was trying to execute it am getting Remote access Issue, I was added the User ID into the Azure Active Directory in the SQL Server and using the same User ID for authentication purpose. Below are the Error and the Script which am using, any modifications on it please suggest. Note: I have to execute this from the DevOps pipeline. – niranjan reddy Aug 04 '21 at 11:28
  • Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) – niranjan reddy Aug 04 '21 at 11:35
  • That is some sort of connection issue. Have you used the correct server name and port? – SE1986 Aug 09 '21 at 09:40