Linked Server from On-Premise to Azure SQL Database

I am using: SSMS-18.4 SQL Server-SQL 2019 CU3 Windows 10

I was able to create a linked server successfully from on-premise SQL 2017 to the Azure SQL database without exposing my password.

--Read the password from text file  DECLARE @password VARCHAR(MAX) SELECT  @password = BulkColumn FROM    OPENROWSET(BULK 'C:\Azure SQL Database - Where is my  SQL Agent\password.txt', SINGLE_BLOB) AS x     --Drop and create linked server IF EXISTS(SELECT * FROM sys.servers WHERE name = N'AzureDB_adventureworks') EXEC master.dbo.sp_dropserver @server=N'AzureDB_adventureworks', @droplogins='droplogins'; EXEC master.dbo.sp_addlinkedserver  @server = N'AzureDB_adventureworks',   @srvproduct=N'',  @provider=N'SQLNCLI',  @datasrc=N'ugdemotargetserver.database.windows.net',  @catalog=N'adventureworks';  EXEC master.dbo.sp_addlinkedsrvlogin  @rmtsrvname=N'AzureDB_adventureworks',  @useself=N'False',  @rmtuser=N'taiob',@rmtpassword=@password; GO 

But the password is not getting the correct value. I am getting a login failure.

Some of the error message:

Login failed for user 'taiob'.  (.Net SqlClient Data Provider) Server Name: .\SQL2019  Error Number: 18456  Severity: 14  State: 1  Line Number: 1 

If I hardcode the password it works fine. If I print the variable I can see the value is correct. It is not a firewall issues as I can directly connect from the same SSMS that I am running the code from.

Azure elastic pool: Can I create as many databases as I want?

I am sitting here trying to figure out what the elastic pool in Auzre gives me. I have a customer that wants a guess on pricing if the move to Azure. They have 3 databases, on a server. The server has 96 GBs of RAM and a 3.4 Ghz processor. That is all i know. The three databases is sharing these specs, and each database has it own area of expertise:

  1. one database handles all the CMS data
  2. the second database handles all the commerce data
  3. the third database is a database that stores user info, etc – all data that doesnt fit the two others

I think the usage pattern is pretty diverse, so sometimes the commerce is using a lot of RAM and CPU, other times it is the CMS etc.

I dont think they fit well into a single database, because they are going to need three big databases then, and almost all of them is going to be under utilized all the time. So I thought about elastic pools.

The describtion of ealstic pools fits the above very well: all databases shares eDTUs. Perfect! But one thing the docs doesnt describe is: do i pay for the elastic pool AND pr database? Or do I just create databases on the fly, and all of them shares, that is: the databases is free, but you pay for the eDTU.

I have read this https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool – but they don’t describe it.

Hope someone can give me some more info.

How to create two apps in MS Azure with the same Entity IDs? [migrated]

I am needing some technical inputs to overcome a technical challenge with regards to an MS Azure app.

Goes like this…

We have manually configured a non-gallery app in MS Azure which allows a third party party platform to SSO (SAML based) into their app. It works fine no issues. This app allows SSO for the third party platform’s Australia based staff.

However, I am now needing to configure another app for the same third party platform in order to support SSO for their New Zealand based staff. However, the ‘Entity ID’ that the third party has provided me for this second app is the same. The reply URL etc. are different.

In MS Azure all Australia and New Zealand staff are in the same Azure instance. My technical team is now faced with a big challenge as they are unable to create the second app (in MS Azure) as the ‘Entity ID’ for two apps is the same. How do I work around this uniqueness requirement? How do I configure two apps with the same ‘Entity ID’ in MS Azure?

Any help would be greatly appreciated.

Reporting services 2008 reports connection to azure sql database

Due to the end of support of sql server 2008 R2 we are planning to move the database to azure as a paas service (azure sql database). Right now the application server has reporting services 2008 R2, for now it won’t be moved to the cloud or upgrade his version. The users create the reports, then they upload them to reporting services and generate the desired information, My question is this: since azure sql database uses the latest version of sql server do the users or myself have to redo the reports in a newer version of report builder or visual studio data tools, or the reports will work the same way and the only thing I have to modify is the data sources.

What does these errors mean for Azure SQL Database?

Yesterday my app could connect to my Azure SQL Database. Today, it is having problems. The app logs this error whenever it tries to connect:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The received certificate has expired.) The received certificate has expired.

I tried to connect through SQL Server Management Studio (which usually works) and got a different error:

Database ‘master’ on server ‘myserver’ is not currently available. Please retry the connection later. If the problem persists. Contact customer support, and provide them the session tracing ID of ‘{}’. (Microsoft SQL Server, Error: 40613)

I followed the instructions and contacted Microsoft Support (currently waiting for a response).

I found others having the same problem. This PowerBI user got the same error and solved the problem by modifying their reports to not require encryption. I tried unchecking the “encrypt connection” option in SSMS but the error remained.

enter image description here

This ConfigMgr user solved the error by generating a new certificate in SQL Server Configuration Manager. I’m using Azure SQL Database, so I don’t have that tool available.

What am I doing wrong?

SSMS 18.4 Error opening database properties dialog box Azure SQL DB Managed Instance

I have three databases in an Azure SQL DB Managed Instance. I am using SSMS 18.4. One database I can right-click, choose Properties, and the dialog box comes up normally. The other two databases give me the following error.

enter image description here

I have spent a couple of hours Googling with no results. Anyone have any ideas?

CORS issue with WebApp on Azure + AAD

I would like to get some experts’ advice regarding the way I resolved the following issue:

Let’s say my personal app is hosted as an Azure App Service on https://example.azurewebsites.net The JS client code running withing the user’s browser does some GETs on the API served at https://example.azurewebsites.net/api/endpoint

const myUrl = "https://example.azurewebsites.net/api/endpoint" const myHeaders = {} const myInit = {     method: "GET",     headers: myHeaders }  fetch(myUrl, myInit) 

For his first connection on the web page, the user has to authenticate to the Azure Active Directory, then he can browser freely.

Problem:

The fetch calls to the API get redirected to login.windows.net and the browser blocks the replies as per CORS.

Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://login.windows.net. (Reason: CORS header ‘Access-Control-Allow-Origin’ missing) 

My solution:

I believe the fetch redirection happens within azure, before reaching my backend (no app logs were visible). I added the field credentials: “include” to myInit in order to pass azure authentication check. It works now, no redirection to another origin happens, the requests reach the backend and the replies reach the client 😀

const myUrl = "https://example.azurewebsites.net/api/endpoint" const myHeaders = {} const myInit = {     method: "GET",     headers: myHeaders,     credentials: "include" }  fetch(myUrl, myInit) 

I am still beginner on Azure, and I got this working by chance. Do you think it is the right approach ?

Investigate potential breach in Azure App Service

I asked on Server-Fault, but have not received any response so thought I would try here.

We suspect we have had a data breach, but we are not sure how to investigate it to determine the source of the breach or what data was sent.

We have an app service that has been running for a while with steady usage. We noticed that over the last couple of nights there have been large spikes in data out. Our website has an authenticated user area and we are concerned that there may have been a breach or something unauthorized happening on the site.

The site has consistently always had below 10MB/15mins Data Out. But the sudden spike was over 180MB then instantly back down again. The second night the spike was 600MB. In the same 15 minute metric window the Average CPU time spiked to over one hour. Response time, number of requests and 4xx/5xx errors all remained steady.

Azure metrics graph

Is there a way using Azure (Metrics or Security Center) to determine what caused the massive spike in Data Out? What data was sent, who it was sent to etc? Is there anything we can enable within Azure to allow us to view this data if it was to happens again tonight? (e.g. Azure Sentinel)

Looking at other metrics, there was no obvious spike in 4XX or 5XX errors or number of requests, so we do not suspect a brute force or DoS attack.

Can one use a certificate directly from Microsoft Azure Key Vault for LDAP/S?

The only method I can seem to find to add a certificate for secure LDAP (LDAP/S) for Azure Active Directory Domain Services is to upload the certificate from my local computer. This seems like a very poor key management solution when Microsoft Azure Key Vaults is available for creating and storing key pairs and certificates. Am I missing something? Is there a way to directly use a certificate and key pair from a Key Vault or must I download these from a Key Vault and then upload them for LDAP/S? Best PKI practices dictate that I never access the private key directly.