Can you connect Power Apps to SQL Server if you work in the government?

  • My agency has been using the combo of on-premises SQL Server for the back end and local desktop software for the front end.  Now upper management is saying that they want web applications only.  And they really want to use our MS 365 license rather than have to pay for anything else.  I'm investigating Power Apps to see if Power Apps can meet our front end needs.  I tried connecting Power Apps to SQL Server and ran into a note saying that a "Premium" license is needed for the SQL Server connector.

    My manager is willing to pay for a Premium license, but when I went to our licensing person, she said that she's not sure if it's even possible for government agency/license to use that connector.  I tried asking this question of CoPilot a couple different ways and was not able to get an answer on yea or nae.

    QUESTIONS:

    1. Does anyone out there work for a government agency who is connecting Power Apps to SQL Server?  If so, do you know which license you are using?
    2. Do you have any recommendations on how to get a good answer to this question?

    ***

    And as an aside, what do you think would be good front-end web software to use for local applications which have a local SQL Server back end?  Or maybe we could go to Azure for the back end, but I'm still not sure what would make the most flexible/modern front-end software for browser-based applications.

     

    • This topic was modified 1 month ago by  JJ B. Reason: typo fix
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • My opinion (note I am NOT a licensing expert) - that falls under an "it depends" scenario. Microsoft probably doesn't care, but your government agency/license may be unhappy with their data sitting in the cloud, permanently or temporarily and MAY require you to keep all of the data on site.

    To determine if Microsoft will care, you should reach out to your Microsoft rep or the reseller point of contact you worked with to get your licenses. To determine if the government agency will allow it or not, you'd need to reach out to their legal team and get someone above you to sign off on it.

    I would 100% NOT trust AI, random forums, or advice from anyone not from Microsoft or your internal legal team on it because IF the advice is bad, AI won't get in trouble. The forums won't get in trouble. It'll be your neck on the line. Are you confident that the advice you got is 100% accurate for your specific scenario? Talk to your rep and get the advice from them on if it is acceptable from a licensing standpoint and your legal team to determine if it is acceptable for your business to use it. That way IF something goes wrong, you have advice from someone you can talk to.

    My second bit of advice is are you SURE web apps are the right solution to your problem? IF the apps are used internal-only and used on single platform (ie Windows only apps), I would be very hesitant to migrate to web apps. Main reason being web apps, from my experience, are a lot more work to build and maintain. Security is a whole different beast with a web app vs local app as your web app needs an authentication screen. Local apps can just inherit the currently logged in users credentials. Plus, you have to plan for redundancy - server crashes, ALL users are unable to use the app. Local apps one desktop crashes, one user is impacted. You also have to plan appropriately - web apps mean that the majority of the workload for the app is running on the server. So if your app uses 1% of the CPU per user on average, 100 users may end up using 100% CPU (just an example). Plus debugging and maintenance is a bigger pain in the butt. And cross browser compatibility can be tricky. Edge and Chrome are pretty easy as if it works in one, it will USUALLY work in the other. But then you get Safari and Firefox into the mix, and then all the obscure browsers and your web app suddenly becomes a maintenance nightmare. The worst part is the "works for me but doesn't for end user" bugs. Is it a browser configuration issue? browser addon? firewall? version issue? latency? etc. debugging web apps is a slow and painful process. Local apps you pop it up in the debugger and replicate the issue. If it works for you and not them, build a test version with additional logging so you can narrow down the problem.

    Now, I'm not saying web apps are bad - if you need cross platform (Windows, Mac, Linux, Android, iOS, toaster, etc.) web apps are MUCH nicer than having multiple code bases to maintain.

    As for good front-end web software - I recommend whatever you and your team are familiar with AND what makes sense for the application. I would recommend trying to stick with what you know rather than learning something new where possible. I've done some quick and dirty web apps in C# in ASP.NET and have been happy with them. What I mean by makes sense for the app is if I wanted to offload as much processing as possible to the client rather than overwork the server, I'd be looking at something like javascript where most of the processing is done client side. Downside is it exposes the source code to the client, so you may not want javascript. It REALLY depends on your requirements, but I would be pushing back on the business to determine if this is a valid request or if it is a "management" request who heard buzzwords like "webapps" and "cloud based" and assumed that was how all apps should be developed. I know pushed back on my former boss when she suggested we change all of our apps to be JAVA based because then it would be cross platform and I had to explain to her that's not how that works and that it'd be a lot of work and training and overall not worth it since we don't need the apps we had built at the time to be cross platform. Web apps came up too and that got shot down almost as fast as the convert to java initiative.

    Pick the right tool for the job and you will be much happier going forward.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • @Mr. Brian Gale

    Oh my gosh, much of your reply is SOOO helpful.  Thank you for taking the time to type all that out.

    re: "My second bit of advice is are you SURE web apps are the right solution to your problem?"

    NO!  I'm not sure of that at all.  What happened is that management hired a consultant to review our systems.  I was kept out of that process.  After the consultant process was over, I was told that the result was that we have to get rid of desktop applications.  No one asked my opinion, and I was told that my opinion was not welcome and I could even get in trouble for offering it.  So...

    That said, I think that there may be some time in the future when my opinion may be sought and now, using your post, I have a nice list of points to bring up that didn't just come from me/someone whose opinion they are not valuing.  As you say, it is just coming from someone on the internet, and we don't know you, but I can do more research and hopefully make the point that this isn't just my opinion.

    To clarify: I think web apps may actually be the right solution for some or maybe even all of our business needs.  It's just that if I got asked up front what I thought we should do, I would not have started with the end-conclusion that we need to switch everything being web apps.  I would have gone through a careful analysis of the pros and cons of web vs desktop apps and then compared the pros and cons with our business needs.  Then I would have been able to make intelligent, evidence-based recommendations on a case by case basis.  I did not get the sense that that was what the consultant did...

    Thank you again for your post.  It is extremely helpful.

  • UPDATE: Because I'm not having any direct contact with our "Dell rep" who advises us on Microsoft licensing and because I'm worried that the specific business need may not have been clearly communicated, I've spent some time since my original post above trying to find out more about government licensing.

    I finally found out that there are 2 types of government licenses, labeled G3 and G5.  It looks like G5 *does* include the SQL Server connector in Power Apps.  So, the short answer to my question is that we should be able to get that functionality under a government license, but we will have to pay for the G5 license.  My agency's licence person is in the process now of trying to get our Dell rep to confirm that the G5 license will do what we want and figure out what it would take to get me the G5 license for testing purposes.

    FYI: Note that the project to convert to web apps, if we decide to use Power Apps, could end up costing us quite a bit of additional money (in addition to all the issues that Mr. Brian Gale came up with), because it would not just be the developers who need the G5 license.  MS set it up so that all the staff in our agency would need the G5 license.  I don't know the exact cost yet, but I'm guessing that it would be a big bump in cost over what we have now with our desktop apps.

    We have a lot of important data and SSIS / database merge functionality in SQL Server.  I can't imagine how we would move forward if we didn't pay for a SQL Server connector and if management wants the front-end to be Power Apps.  I can't imagine trying to use Sharepoint or other G3 level option as a database...

  • Just a little FYI too - when hiring a consultant, it never hurts to check what the consultant is an expert in. If you hire a consultant to evaluate your applications and the consultant is an expert in web apps, they are going to tell you to switch to web apps because it is what they are familiar with.

    There is also the approach of "if it ain't broke, don't fix it". If your apps are working, it MAY make more sense to focus your efforts on new stuff and quality of life improvements.

    IF your company really needs to switch to web apps, I would recommend you build up a proof of concept and get end user buy in before you convert everything. Web apps CAN be a pain in the butt for things like multi-session management. What if the end user has the app open in 2 tabs? Can they do 2 different things in 2 different tabs, or will that cause issues? If it causes issues, how are you going to "lock down" the app so they can only have 1 tab open? Desktop apps that's easy as the app can be designed to have only 1 running instance or allow multiple instances to be run.

    Web apps can also have problems with performance. We have a 3rd party web application (hosted by the 3rd party too, so we have no control over the application or resources) that we recently switched to from a much older on-premise solution and some days it runs great, other days we get intermittent 500, 502, and 503 errors. Some days logging in is near instant, other days it is a 30+ second wait for it to finish the authentication process. And there is not a darn thing we can do to make it faster. The server and hardware are all managed by the 3rd party. The 5xx errors that come up are server-side errors so we have no control over those and we just have to retry the operation and hope it succeeds the second time.

    In your scenario it sounds like you may control the web host, so if you find performance is slow, you can investigate (review logs, look at resource utilization, etc.) and go from there.

    I am NOT trying to say web apps are a bad idea - it is just good to make sure your ducks are in a row before switching. I recommend doing a proof of concept with a simple program first and after a demo to end users, if they like it, let them try it out for a little bit WHILE the desktop app is still an option. They have 2 options to use the system for a while and you can monitor usage. Do people prefer the desktop app or the web app? Do you get more support requests for the desktop version or the web version? If it is well adopted, turn off the desktop app and work on migrating more. I would do 1 app at a time so you can monitor usage of the app and monitor resources on the server. Don't want to crash the server because you overload it putting all of your apps in there at once. Also don't want to overwhelm users by putting all the apps to webapps immediately.

    Also, as an alternative to web apps, there are also remote apps. These are "local apps" that run on a server but are presented to the end user like a regular app. It uses the RDP protocol and requires user CAL's on the server, so it can get expensive, but it runs the app on the server and allows the end user to run it with a look and feel like it is running on their desktop. Microsoft has their own tools for setting it up or there are open source tools that can do it too that make the process a lot more simple, but support is lacking. I am actually a maintainer for one such tool and in my opinion, the tool is pretty nice for a free tool. Not sure if I can send the link without it getting flagged for spam mind you, but the tool is "remoteapptool" and it is in github. Kimm Knight wrote it, I just help support it and help with bug fixes when I have free time. If you google "github kimm knight", the first result is the repo for it.

    Remote apps MAY not be what you need mind you. If the app is a single-instance app, remote apps won't work. If the server isn't configured for multi-user, remote apps won't work. If you can't get budget for user CAL's on the server, then remote apps won't work. But never hurts to throw another option in the mix. Remote apps have similar advantages to web apps - single location where the app is hosted, so updates only need to happen on 1-2 machines (primary and failover or secondary... maybe more depending on if you setup a load balancer and such) and all users get the update at the same time. One big pitfall to remote apps though is that if you need to update the app, all users must exit the app for you to update it. Plus, since remote apps run on the server rather than the desktop, end user computer configuration doesn't impact them. A laptop with 4 GB of RAM will run the app roughly the same as a desktop with 32GB of RAM. It is just your network connection and server load that determines the performance of the application. The other plus is that remote apps, like web apps, can be cross platform pretty easily as long as the client support the RDP protocol. So Android, iOS, Mac, and Linux all support RDP via 3rd party apps, so you can run remote apps from them.

    Just throwing another option out there for you. Again, not saying web apps are the wrong approach, I just find that they have a lot of prep work to ensure that things work. Remote apps and local apps both allow you to keep using your current skillsets without needing to learn new tools.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • @Mr. Brian Gale:  Another great post.  You are preaching to choir with your opinions, but you have more details to back up the points.

    FYI: I was on my way to trying to do a proof of concept web app via Power Apps and the first problem I ran into was that I couldn't even connect to my database...  Hence the original post above.  I did try to figure it out for a couple weeks before posting the question.

    Thanks for the tip about remoteapptool!  I'm going to look into it.  🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply