Posey's Tips & Tricks
Challenges With Using RemoteApp to Host Custom Applications, Part 1
Hosting a custom PowerShell application through RemoteApp can introduce unexpected authentication and database permission challenges -- especially when Windows authentication and SQL Server backups are involved.
In a recent series of posts, I explained how to deploy the Windows Server RDS service for the purpose of making applications remotely accessible through RemoteApp. I initially delved into RemoteApp because there were some Windows applications that I needed to be able to access from non-Windows devices. While I was ultimately successful in my efforts, I found that there were some challenges associated with making custom applications work properly in a RemoteApp environment. As such, I wanted to take the opportunity to talk about the issues that I encountered and how I solved those issues.
In my own organization, I have a custom PowerShell application that serves a mission critical purpose. This application has to be run interactively because it is a GUI-based application. That application also has a dependency on a SQL Server database that is being hosted on another server. While making such a script work with RDS might not seem to be all that challenging, making it work was a surprisingly complex undertaking.
The first challenge was enabling access to the database. The easy option would be to use SQL authentication and then embed SQL credentials into the PowerShell script. However, my script contains a function that is used to back up the database. SQL accounts have no access to the filesystem, meaning that backups will fail. As such, Windows authentication is the only option.
It's a little bit off topic, but the backup issue ended up being the single most difficult problem that I had to solve. I will likely discuss this issue at length in a future blog post, but let me give you the short version...
My PowerShell script is running on the RemoteApp server and SQL Server is running on a different machine. When my script performs a database backup, it causes the backup files to be written to a folder on the SQL Server. That's because even though the backup was initiated by PowerShell it is actually SQL that performs the backup and SQL Server has no knowledge of my RDS server. To get my backups to the right place, I had to share the folder where the backup files are stored and then modify my PowerShell script to copy the files from the backup folder to a mapped network drive.
So let's get back to the permissions issues. As I mentioned, using Windows authentication is the only option if SQL is going to need access to the file system. Interestingly, when Windows authentication is used, you cannot embed credentials into the SQL Server connection string. If SQL Server sees credentials in the connection string, it will assume that those credentials are to be used for SQL authentication, not Windows authentication. I spent a lot of time figuring that one out.
When a PowerShell script uses Windows authentication to connect to a SQL Server database, it adopts the credentials of the user who is currently logged on. In the case of a script that is running on an RDS server, you can't force the remotely hosted PowerShell script to run under the context of a service account. Instead, a remote application runs under the context of the user who is running the app.
With this being the case, the first thing that I had to do was to grant my domain users permission to access the SQL Server database.
To get started, open Active Directory Users and Computers and create an Active Directory group containing all of the users who will need access to the PowerShell script and the underlying SQL database. For the purposes of this article, I am calling this group RDS_SQL_USERS.
With the group created, open SQL Server Management Studio and connect to the SQL Server. Navigate through the console tree to Security > Logins. From there, right click on the Logins container and select the New Login command from the shortcut menu. When the Login screen appears, click the Search button, change the location to your domain, and then enter the name of the group that you created. Click OK and then make sure that the authentication type is set to Windows Authentication, as shown in Figure 1 below. Click OK again to complete the process.
[Click on image for larger view.]
Figure 1. I Am Mapping A SQL Server Login To A Group.
The next step in the process is to map the group to a database user. The easiest way to do this is to click the New Query button and then enter and execute the following query:
USE MyApplicationDB;
CREATE USER [DOMAIN\RDS_SQL_Users]
FOR LOGIN [DOMAIN\RDS_SQL_Users];
In this example, you will need to replace MyApplicationDB with your actual database name. You will also need to replace the word DOMAIN with your actual domain name.
So now the users within the group should be able to complete the authentication process, but the users do not yet have permission to do anything. The way that you would grant permission to the users is going to vary depending on the permissions that you want to convey. For standard read / write permissions, the query looks something like this:
USE MyApplicationDB;
ALTER ROLE db_datareader
ADD MEMBER [DOMAIN\RDS_SQL_Users];
ALTER ROLE db_datawriter
ADD MEMBER [DOMAIN\RDS_SQL_Users];
Once again, you will need to replace MyApplicationDB with the actual database name and replace DOMAIN with your domain name.
So now that I have discussed the issues that I encountered with regard to SQL Server permissions, I want to turn my attention to the challenges associated with making PowerShell scripts work in an RDS environment.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.