Does ProGet support Azure SQL databases?

Guest

I'm attempting to install ProGet on a VM in Azure using a SQL Azure database for the storage.

I have a working Azure SQL Server.
I created an empty SQL Azure database called proget
I created a login for a user proget and made that user a db_owner on the proget database.
I verified that I can use SQL Server Management Studio and the proget user credentials to connect to the SQL Azure proget database.

During install I set my connection string per the recommendations in the Azure Portal, substituting in the user credentials I added (line wrapped for readability:

Server=tcp:myserver.database.windows.net,1433;
Database=proget;
User ID=proget@myserver;
Password={password};
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;

The first time I tried this, I got an error message that the proget user didn't have the ability to connect to master. This seems to be a SQL Azure thing, so I added a proget login to master with no rights.

Trying again, I got this message:

Database proget does not exist and the current user does not have the privileges required to create it.

I know the database exists, though it's empty and needs the installer to populate it with tables and such. The user has full db_owner permissions on that database so it should have rights... it just doesn't see the database.

Is SQL Azure supported? Or am I doing something wrong?

There is a similar question for BuildMaster but I'm getting a different error so I figured I'd ask a new question.

Product: ProGet
Version: 3.8.6

Guest

Can you test the following queries that are used by the installer to test the database's existence and valid privileges:

SELECT DB_ID('proget')

and

SELECT CAST(IS_SRVROLEMEMBER('dbcreator') AS BIT)

Guest

Unfortunately, those queries don't work in SQL Azure.

SELECT DB_ID('proget') yields NULL despite me looking right at the proget database in SSMS. This appears to be a known thing that doesn't work in SQL Azure.

SELECT CAST(IS_SRVROLEMEMBER('dbcreator') AS BIT) yields 0 because there are no server roles in SQL Azure. It does this even if I'm logged in as the SQL Server administrator with full rights to do anything. This is also a known thing.

Is there a workaround or a way to switch the connection string after the fact? I suppose I could always deploy with SQL Express, export the script to create the database contents, do that creation, and update the connection string of the installed ProGet after the fact...

...though it'd be nice if the installer would just "do the right thing" so I could avoid it. :)

Guest

That would explain it then! One of the first information boxes on MSDN for DB_ID is: "Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database,..." so we assumed that would work.

The current workaround then would be to install to another database like SQL Express, then backup/restore the fresh database into SQL Azure.

Once you've done that, edit InedoLib.DbConnectionString in web.config for the web app and ProGet.Service.exe.config for the service to both point to Azure, then restart the web and service.

I'll also put in a note to find another way to get it working on Azure.

Guest

As it turns out, you can't just back up a SQL Server or SQL Server Express database and restore it on SQL Azure.

What worked for me:

Install ProGet with SQL Express and let it create the database.
Create an empty ProGet database in SQL Azure along with a SQL login/user that has access to that database.
Use the SQL Database Migration Wizard to automate the move from SQL Express to SQL Azure.
Update the web and service connection strings as you mentioned to use the SQL Azure database with the SQL authentication credentials for the proget user.
Restart the web app and service.

Thanks for the help on this one. Seems to be working like a champ now.

Guest

While this technically works, it makes installing and upgrading Proget very cumbersome. Since this is from 2015, and SQL Azure has been getting more traction, is there any progress on a fix for this?

Note that it seems that ProGet works fine once the SQL Azure database is built. The problem is strictly in the installers.

Guest

I'm not sure when we will be prepared to "formally support" this; i.e. properly documenting, setting up testing cases, running those before we deploy, making sure that we change the installer as soon as Azure rules change, etc.

Because of that, we haven't researched how to make installation simpler, let alone looked at the code and tried solutions ourselves. We would be happy to provide the community with access to our code (while we work towards open sourcing everything) to suggest ways to make life a little easier for these sorts of use cases.

If it's as simple as changing a query here and there, and it will still work with our supported SQL Server versions, then sure, why not? We just don't know Azure SQL well enough to know how simple or complex this would be.

Guest

It seems that the installer is just making some calls that aren't supported by Azure. I doubt they are required by the installer, they appear to be checks for DB existence, etc.

In addition, it seems the installer tried to connect to master directly instead of the database it was told to in the connection string. In Azure, that behavior is blocked by default since the main user doesn't have access to master.

Of course, I can't see what it does beyond this point since I can't get by it.

With those small changes, hopefully you'd be able to unofficially support Azure.

Guest

That sounds about right, but I'm not so familiar with the installer (or the Azure side) for that matter.

Anyway, I gave you access to our source code on GitLab... if you have any ideas on what we can do to better (unofficially) support Azure in this case, please share. Maybe it's something as simple as like, a try/catch/try/catch to test for Azure-compatibility.

I think, it might be this repository https://gitlab.com/inedo/ProGetInstaller, but it could also be the Inedo.Installer project as well that does the database testing.

Guest

Was there any progress made with getting the installation checks working for Azure. I can't access the gitlab repo to investigate myself?

Guest

We are addressing this now, and should finally have a better experience for installing with SQL Azure as of ProGet 5.2. (Logged as PG-1490)

jere.vekka_5487

I've been testing this now and it seems that it is not working yet.
Like mentioned already, the installer gets the connection to database, but error is thrown on summary:
"Database progetdb does not exist and the current user does not have the privilege"

I try to test the previous workarounds later when I have more time.

atripp

We made and tested several changes to the installer a while back, but it's not something we regularly test/verify.

Please share what you find work! Thanks.