Kirx' Blog - kirxblog.wordpress.com

Use App-V 5 Beta Publishing and Management Server with a Remote Database | April 18, 2012


App-V 5 Beta allows to connect to a Database that is installed on a remote machine. However, it has shown that using a remote SQL Database is not that trivial. This article will highlight how to prepare the remote SQL Database to be used by App-V 5.

Reading through the documentation there is no hint that connecting an App-V 5 Publishing Server to an SQL database that is running on a different machine might be sort of ‘special’. Instead, it states something like “just enter the name of your Database Server’ and that’s it. Well, if you read some forum articles, posts and tweets it appears that this implementation does not exactly provide an ‘out-of-the-box’ experience. Let’s see…

Related Articels

2012-04:  Use App-V 5 Beta Publishing and Management Server with a Remote Database (this article)

2012-11: Remote Database Preparation for App-V 5 (5.0 RTM and SP1)

2013-12: Remote Database Creation for App-V 5 Revised (5.0 RTM and SP1)

2015-04: Remote Database Creation for App-V 5 SP3

 

 

Before the pre-beta release, I tried using a remote-DB with several pre-versions – and with different success. Well, I don’t want to bore you with the full history, let’s focus on the actual Beta (App-V 5.0.0.536).

In App-V 5, there are two App-V Server sub-functions: “Publishing” and “Management”. The “Publishing” server does communicate with the “Management” server, but not with the Database. Therefor, the on-going description refers to the “App-V Management Server (App-V MS)” and not to the Publishing Server. Indeed this differentiation not interesting if both services run on the same box (I used a ‘fully distributed’ scenario).

System Requirements for the App-V 5 SQL Database

From the Beta AdminGuide

  • Windows Server 2008 R2 (or Higher)
  • MS .Net 4 Extended Version
  • MS .Net 3.5 (for PoSH support)
  • MS SQL Server 2008 SP2 or MS SQL Server 2008 R2 (Standard, Enterprise or Datacenter)

Not (explicit) in the Admin Guide

  • Do not install the SQL Server on an AD Domain Controller (well… of course in productive environment this would be a no-go implementation anyway – but for test/eval you might be thinking of that… don’t do that!)
  • Installing .Net 3.5 on a Windows Server 8 box might throw an error. Use this guide, then reboot the machine
  • Only use Windows Authentication against the SQL server (MS does not like the ‘old’ SQL native authentication and I don’t like it either)
  • All machines have to be domain-joined (Don’t ask me if it would work in separated, but trusted domains)

Additional Simplifications made for the test

  • Windows Firewall is deactivated on the all machines (it was a fully-isolated network on a single Hyper-V host, so I don’t feel filthy about that here)
  • All machines are members of the same domain
  • For some services/actions, I use a Domain Admin account, so I didn’t excessively divide/assign user rights

Preparation

Along with the Beta bits, MS provided a set of “SQL Database Scripts”. We’ll need them.

Prepare the SQL Server

Install .Net 3.5 on the SQL Server box (issues? follow this guide)

Install one of the supported versions of SQL Server on one of the supported Operating Systems. I was using SQL 2008 R2 on a Windows Server 8 Beta box, quite a ‘normal’ installation. You need

  • SQL Database Engine (to host the Database)
  • SQL Tools (Enterprise Manager) that are required for some of the upcoming steps

Prepare the App-V Management Server

Install the App-V MS System requirements.

On Windows Server 8 Beta

  • start with the .Net 3.5 installation, then reboot the machine
  • some IIS feature may not have the same naming as on Win2008R2. Be smart here. At Sebastian’s Blog, search for the “dism.exe” command to make it right.
  • Well, not that the Admin Guide tells you that: For the Management Console to run, you’d need Microsoft Silverlight 4 (or higher)

Other Preparations

User, Computer and Group accounts

I used/created the following accounts and groups. Here, I loosely follow the “AGDLP” approach

  • L-SQL-AppV-Writer  (Members of that Domain Local group will get the right to modify/write data to the App-V SQL Database)
    • Members: G-AppV-Admins
  • L-SQL-AppV-Reader (Members of that Domain Local group will get the right to read certain data from the App-V SQL Database)
    • Members: G-AppV-Admins
  • G-AppV-Admins (Members of that Group have the “App-V Admin role”
    • Members: Admin, <App-V PS Computer Account>
    • Member of: the two “L-“ groups
  • User “Admin”: This is a (Test/Eval) Domain Admin. Essentially, that user has to have local rights to install applications and it has to be a domain member
    • Member of: G-AppV-Admins, Domain Admins
Important: As you can see, I added the App-V Publishing Server’s Computer Account to the “G-AppV-Admins” group. While this is not exactly right from the AGDLP perspective it is important that the App-V PS’ machine account also gets Modification Access to the SQL Database”!

Execution

OK, so now that we have most things ready, let’s get started. In fact the first steps have to be performed before the actual App-V installation but I sorted them into the ‘main block’ because they are quite specific… Don’t judge me on that.

SQL Script Preparation

App-V 5 allows two methods to prepare the SQL Database.

Option One

is to execute the ‘Management Database’ function of the App-V Installation Wizard. Hmm, I think I easily can imagine who an SQL Admin reacts when you go to him/her and ask “I have that Setup.exe that prepares an App-V Database… I need to execute it on your SQL Server (cluster node)”. If you’d hear laughing or shouting, you’d be lucky. I suppose you’d hear the noise of the door that closes behind you (or you see the neighboured cubicles passing by under you).

Option Two

consists of some SQL scripts that can be prepared and then can be given to the SQL Admin. Because SQL scripts essentially are plain text (and human readable for SQL people) it will be much easier for you to let them execute against the SQL Server.

So, extract the SQL Script Archive onto a temporary location. You should something like this:

image

In these scripts, you have to make two adjustments

Database.sql
/***********************************************************************************
**  Copyright (c) 2012 Microsoft Corporation.
**  All Rights Reserved
**  Purpose:
**          Creates the AppVManagement database
************************************************************************************/
RAISERROR('Creating database', 0, 1) WITH NOWAIT
GO

/* Create the database */
CREATE DATABASE [AppVManagement]
GO

RAISERROR('Setting ALLOW_SNAPSHOT_ISOLATION on the database', 0, 1) WITH NOWAIT
GO

/* Set ALLOW_SNAPSHOT_ISOLATION on the database */
ALTER DATABASE [AppVManagement] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Here, replace “[AppVManagement]” two times with the name your App-V Database should get. Because its a text file, you can use Notepad for that.

Important: Do NOT include the square brackets into the replacement string.

In my Test, I replaced “[AppVManagement]” with “AppV5Beta” (without “”)

Hint: If you want to become the SQL Admin’s friend… ask how the database should be named.

Permissions.sql

Also we have to adjust the “Permissions” SQL script. It’s a little bit more complicated here, but we can make it.

First, determine which user (group) should be used to read- and change-access the database. In the guide, MS doesn’t really explain which services/feature require read or modify access, so I decided to use the same account for both access scenarios (and I recommend the same for you).

Hint: Do NOT use an account that is already added to the SQL Server. Create a new account for that! During my tests I added a single user (admin) that already was added to the SQL server before (as “THE SQL Admin”)… and SQL threw an error (Account already used, which was fine) but removed the previously know account right away. Did I mention It was the SQL account?… Yeap, it got removed and I lost contact to my SQL server. Snapshot Revert was my troubleshooting approach.

I do recommend using Groups (instead of Users) to add here, because it gives you more flexibility. We created a group L-SQL-AppV-Reader before… apparently this is a good “Read Access” example. And we will use L-SQL-AppV-Writer for the Modification Access.

You have to enter the Account Name and (normalized) SID into the Permission.sql script. I used the following PoSH lines to get the SID fro the L-SQL-AppV-Reader group taken from Technet:

$objUser = New-Object System.Security.Principal.NTAccount("L-SQL-AppV-Reader")
$strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value

The result contains something like “S-1-5-21-1191135933-289200372-2779666654-1003”

Remove the “S” and the dashes, add an and you’ll get “1521119113593328920037227796666541003”. This is a string we need.

So, opening the Permissions.sql you get

/******************************************************************************************************************
**  Copyright (c) 2012 Microsoft Corporation.
**  All Rights Reserved
**  Purpose:
**          Creates user logins, sets up database roles and access to specific tables.
** 
**          NOTE: PLEASE REPLACE ACCOUNTS AND SIDS FOR READ AND WRITE ACCESS BEFORE EXECUTING THIS SCRIPT   
**
**          ManagementDbPublicAccessAccount should be the account corresponding to the user who
**          will be installing Management Service.
**
**          ManagementDbWriteAccessAccount should be the account corresponding to the remote server
**          where Management service will be installed. In case the service is going to be installed
**          on the local server, please use the following values,
**            "NT AUTHORITY\NETWORK SERVICE" for ManagementDbWriteAccessAccountName
**            "010100000000000514000000" for ManagementDbWriteAccessAccountSid
** 
******************************************************************************************************************/

RAISERROR('Granting access on SchemaChanges table to Public role', 0, 1) WITH NOWAIT
GO

/* Grants access on SchemaChanges table to Public role */
GRANT SELECT ON dbo.SchemaChanges TO PUBLIC
GO

RAISERROR('Setting up server login and db login for read-access to SchemaChanges table', 0, 1) WITH NOWAIT
GO

/* Setting up server login and db login for read-access to SchemaChanges table */
EXEC dbo.spSetupLogin 0x[ManagementDbPublicAccessAccountSid], N'[ManagementDbPublicAccessAccountName]', 0
GO

RAISERROR('Setting up server login, db login and db roles for write-access to AppVManagement database', 0, 1) WITH NOWAIT
GO

/* Setting up server login, db login and db roles for write-access to AppVManagement database */
EXEC dbo.spSetupLogin 0x[ManagementDbWriteAccessAccountSid], N'[ManagementDbWriteAccessAccountName]', 1
GO

Now, replace the “[…SID]” and “[…Name]” pieces by the “right” ones we just determined. Again remember to remove the square brackets.

After you replaced two SIDs and two Names, you may save & close the .sql file

SQL Database Preparation

OK, now we have to “apply” the SQL Scripts onto the right database(s). Actually,  probably it won’t be you (but the SQL Admin) to do that. The readme.txt provides instruction where an when to apply the scripts.

There are several tools you can use to apply SQL scripts. I used SQL Server Management Studio.

Run the “Database.sql” against the MASTER database. This will create the App-V Database (that doesn’t exist before)

Run the other SQL scripts against the new App-V database in the following order:

  • CreateTables.sql
  • CreateStoredProcs.sql
  • UpdateTables.sql
  • InsertVersionInfo.sql
  • Permissions.sql

Ensure the script run without errors!

After that, you should have a prepared App-V 5 database that contains several tables and has (group) accounts assigned with access rights.

image

App-V Publishing Server Installation

Fine, after half-an-hour of work we now have a database that should meet the App-V Publishing Server requirements? Time to install? Give yourself a second.

Before starting to install, make sure your installing account (“Admin” in my case”) and the App-V Publishing Server Machine Account are really part of “a group” that has access permissions to the SQL database. I think its wise to reboot the App-V Publishing Server machine right now.

I won’t picture-guide you through the App-V Publishing Server’s installation wizard. Here are the steps to follow:

  • Use “Install” on the first screen
  • Getting started: Accept the EULA
  • Feature Selection: Select only “Management Server”
    • If you want, you can install the Publishing Server as well right away – but my “guide” does not cover the steps for this
  • Installation Location: Confirm
  • Configure: enter your SQL server name and database name here:

image

  • Configure: enter the App-V admins AD Group and a port here (note: not 80 nor 443)

image

  • Click, click Finish

Verification

To verify everything went well, you should launch the App-V Management Console. Basically you can do this from machine with an Silverlight enabled browser. I tested it locally from the App-V App-V Management Server machine. Note that you have to be a member of the “App-V Admins” group we just specified in the “Configure” step.

Open :Port/Console.html”>http://<ManagementServerName>:Port/Console.html

image

No Errors? Cool, we are done!

Error Handling

Yes, you may get some errors. Usually, the actual installation goes quite well but error pop up a few seconds after you opened the Management Console.

When you ‘forget’ to grant the machine account access rights to the DB, the Management Console will complain about that:

image

(by the way this is the same error that you got when I tried to run the database on a domain controller machine)

Sebastian Gernert reported some more errors:

“The operation failed as the Management Server was not found”… register ASP again with IIS (aspnet_regiis)

“Invalid input was passed <Server name>”: Use the FQDN instead of the NETBIOS name when adding a new Publishing Server to the Management System

Summary

Installing App-V 5 with a Remote SQL Database is possible, however not out-of-the-box.

Key takeaways

  • Don’t even try to run the remote database on a AD Domain Controller Box
  • Use Microsoft’s SQL Preparation Scripts to prepare the Database
  • Make sure to grant all App-V Admins and all Management Server Machine accounts write access to the App-V database
  • Be nice to your SQL Admins. They deserve it

Related Articels

2012-04:  Use App-V 5 Beta Publishing and Management Server with a Remote Database (this article)

2012-11: Remote Database Preparation for App-V 5 (5.0 RTM and SP1)

2013-12: Remote Database Creation for App-V 5 Revised (5.0 RTM and SP1)

2015-04: Remote Database Creation for App-V 5 SP3

 

Advertisements

15 Comments »

  1. […] This article was originally published on Kirx' Blog […]

    Pingback by - Cliff Davies — April 18, 2012 @ 19:41

  2. […] Use App-V 5 Beta Publishing and Management Server with a Remote Database […]

    Pingback by The knack » Blog Archive » App-V 5 beta–Other blog posts — April 18, 2012 @ 20:33

  3. I could not find those SQL scripts.. Could you please provide the link to those scripts..Thanks

    Comment by Rohan — April 30, 2012 @ 20:21

  4. “When you ‘forget’ to grant the machine account access rights to the DB, the Management Console will complain about that:” Could you expand on this? Where to look for confirmation. I am receiving the issue mentioned above. I checked in the SQL Server Database and it appears that the machine has access.

    Comment by Adam — June 3, 2012 @ 05:28

    • Got it figured out…Thank you.

      Comment by Adam — June 3, 2012 @ 06:36

  5. The Database Scripts could be extracted from the install executable:

    appv_server_setup.exe /layout C:\Temp

    All .MSI files include the Database Scripts are exported.
    Taken from Microsoft Connect Website

    Try also /? to get a nice GUI

    /vkleinerde

    Comment by Volker Kleiner — October 25, 2012 @ 08:11

  6. […] while ago I wrote an article about what to do if you want to implement a Full App-V 5 Infrastructure – and you’re not the […]

    Pingback by Remote Database Preparation for App-V 5 « Kirx' Blog — November 1, 2012 @ 19:35

  7. Note about the permissions sql script(management db). Be shure you use \ (groupname is also possible)

    Or else you’ll see the following error:

    ‘L-SQL-AppV-Reader’ is not a valid Windows NT name. Give the complete name: .

    Comment by Bart Scheltinga — December 21, 2012 @ 08:06

  8. Hello,

    I am not quite clear on the Publishing server part. I have both Publishing and Management running on the same Server. Do I need to prepare the Publishing server scripts too or just the Management Server ones?

    You say the Publishing server does not communicate with SQL but with the Management server which itself handles the SQL communication. If this is the case what’s the point in the equivalent scripts existing in the extracted SQL scripts folder for the Publishing server?

    Comment by Andrew — December 28, 2012 @ 23:00

    • I suppose you refer to the fact that the ‘server’ installation steps also extract those database scripts to the target directory.
      You only need to adjust the scripts once and run them.
      Both sub folders contain the same scripts (don’t ask me why they are duplicated).

      Comment by kirxblog — January 1, 2013 @ 14:02

  9. Hello!

    How to proceed when i want to upgrade from App-V 5.0 to SP1 -> how do i update the database remotly ?

    The readme.txt in the extracted DatabaseScripts Folder of AppV SP1 do not have a note on that!

    Comment by Mario — September 19, 2013 @ 15:39

  10. […] originally started with an descriptive article about how to prepare the very first App-V 5 Beta implementation within a remote database or […]

    Pingback by Remote Database Creation for App-V 5 SP3 | Kirx' Blog - kirxblog.wordpress.com — April 11, 2015 @ 12:24

  11. […] 2012-04:  Use App-V 5 Beta Publishing and Management Server with a Remote Database […]

    Pingback by Remote Database Creation for App-V 5 Revised | Kirx' Blog - kirxblog.wordpress.com — April 11, 2015 @ 18:19

  12. […] while ago I wrote an article about what to do if you want to implement a Full App-V 5 Infrastructure – and you’re not the […]

    Pingback by Remote Database Preparation for App-V 5 | Kirx' Blog - kirxblog.wordpress.com — April 11, 2015 @ 18:20


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: