How to install the App-V 5.0 Database and Management Server using SQL scripts on the DB
Ryan Cobb | Senior Premier Field Engineer
Hi everyone, my name is Ryan Cobb and here are the steps required to install the App-V 5 Database and Management Server using the SQL scripts on the DB instead of the installation routine.
=====
Background
Environment: Windows Server 2008 R2 domain
Computers:
- AppVDC : Our Windows Server 2008R2 Domain Controller.
- AppvServer : A Windows Server 2008R2 computer running SQL Server 2008 R2.
- AppvServer2 : A Windows Server 2012 computer that is a member server in the domain. All App-V server pre-reqs are met.
Users and groups : AppVAdmin, member of AppV5Admins, Domain Users, Domain Admins.
Tasks:
- Install App-V 5 Management and Reporting databases on AppVServer using the scripts provided by Microsoft.
- Install App-V Management and Reporting services on AppVServer2 using the pre-installed databases.
Installation
First, the installation files need to be extracted from appv_server_setup.exe. This is done by executing the following command in an elevated CMD prompt:
appv_server_setup.exe /layout /layoutdir=c:\extractedinstallationfiles
We will work with the ManagementDatabase first. Inside your c:\extractedinstallationfiles directory is the DatabaseScripts folder, and in here you will find two folders; one for ManagementDatabase and one for ReportingDatabase.
Inside the ManagementDatabase folder there are a couple .sql files that will need to be modified as needed for your environment. The first file is the Database.sql file. The image below is the file as it was extracted.
The next image will show the differences after modification. Because my SQL Server already has both AppVManagement and AppVReporting databases for a different test environment, I need to change the names in the script to something else. For my example here, I will append a 5 to the database names (e.g. AppVManagement5 and AppVReporting5) as show below:
Next up we need to modify the permissions.sql file. It needs to be modified to ensure that the correct permissions are setup for the database. Mainly it adds the name of the App-V admins group, of which a member of that group is used to install the App-V server components on the AppVServer2 computer. In my example, my App-V admins group is named AppV5Admins and the name of the account being used is ManagementDbPublicAccessAccount. The other account that is needed is the ManagementDbWriteAccessAccount account and it is the account of the server used to install the App-V Management components and would be the machine account for AppVServer2.
But only if it was so simple. You see, SQL doesn't particularly like friendly names for user groups or computers so we will need to provide the SIDs for each entity instead.
Using PowerShell and the friendly names of both the group and machine we can retrieve the SIDs. The image below shows the retrieval of the SID for the AppV5Admins group and the AppVServer2 machine account:
The syntax for the above image is:
$Account=”domain\account”
(New-Object system.security.principal.NtAccount($Account)).translate([system.security.principal.securityidentifier]).value
Now that we have the SIDs we can plug those into our permissions.sql file as needed.
Before:
After. Notice how the [ ] brackets were removed. The SIDs as pasted also had the ‘S’s and ‘-‘s (dashes) removed.
I then copied the ManagementDatabase folder to my SQL Server computer which is named AppVServer.
Next, I opened SQL Management Studio, and per the directions from the readme I executed each of the .SQL files in their proper order.
I verified through SQL Management Studio that the commands completed as the DB was added, tables created, etc.
The final test is to install the App-V Management Server service on AppVServer2, which, when given the correct information as far as DB Servername, Instance name and DB Name, all was well in the world!
For the reporting database install, simply repeat the procedure above.
Ryan Cobb | Senior Premier Field Engineer | Microsoft
Get the latest System Center news on Facebook and Twitter :
System Center All Up: https://blogs.technet.com/b/systemcenter/
System Center – Configuration Manager Support Team blog: https://blogs.technet.com/configurationmgr/
System Center – Data Protection Manager Team blog: https://blogs.technet.com/dpm/
System Center – Orchestrator Support Team blog: https://blogs.technet.com/b/orchestrator/
System Center – Operations Manager Team blog: https://blogs.technet.com/momteam/
System Center – Service Manager Team blog: https://blogs.technet.com/b/servicemanager
System Center – Virtual Machine Manager Team blog: https://blogs.technet.com/scvmm
Windows Intune: https://blogs.technet.com/b/windowsintune/
WSUS Support Team blog: https://blogs.technet.com/sus/
The AD RMS blog: https://blogs.technet.com/b/rmssupp/
App-V Team blog: https://blogs.technet.com/appv/
MED-V Team blog: https://blogs.technet.com/medv/
Server App-V Team blog: https://blogs.technet.com/b/serverappv
The Forefront Endpoint Protection blog : https://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : https://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: https://blogs.technet.com/b/isablog/
The Forefront UAG blog: https://blogs.technet.com/b/edgeaccessblog/
Comments
Anonymous
January 01, 2003
ThanksAnonymous
January 01, 2003
Get computer account sid: Get-ADComputer -Filter "name -eq 'ComputerName'" -Properties sid | select name, sid Get ADGroup account sid: $objUser = New-Object System.Security.Principal.NTAccount("domain", "UserAccount") $strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier]) $strSID.ValueAnonymous
January 01, 2003
$computeraccount=”domainaccount$” (get-adcomputer $computeraccount).sid.value When i run it,it shows error.but the following Command is OK. $objUser = New-Object System.Security.Principal.NTAccount("domain", "UserAccount") $strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier]) $strSID.ValueAnonymous
January 01, 2003
We want to install 2 App-V Servers (Management/Publishing/Reporting roles on each) in NLB cluster with using a shared database in Remote SQL Cluster. Which server should be specified as ManagementDbWriteAccessAccount in Permissions.sql ?Anonymous
January 01, 2003
I have an addition, I had the same ‘error’ when running the Permissions.sql script:
Login exists with wrong SID, droping it...
Creating login...
Finished login creation phase.
Remapping orphaned user...
Turns out, SQL uses other syntax to store the SID, so you need to calculate it in another way in order to have the same SID calculated that SQL Server uses. I use the following PowerShell code to calculate the correct SID:
$Account="YourDomainYourAccount"
$NTAccount = New-Object System.Security.Principal.NTAccount($Account)
$SID=$NTAccount.Translate([System.Security.Principal.SecurityIdentifier])
$c = New-Object ‘byte[]’ $sid.BinaryLength
$sid.GetBinaryForm($c, 0)
$c | ForEach-Object -Begin { $NewSid=’0x’ } -Process { $NewSid += «{0:x2}».ToUpper() -f $_ } -End { $NewSid }
This gives the same SID as SQL server uses, and prevents the ‘error’ returned.
Greetz, Ben van ZantenAnonymous
May 22, 2013
Thanks for the information, it helped in my implementation. There was much value in seeing the pre and post changes to the scripts, as thats what I was unsure of. One tip -- while it is displayed in your powershell clip, it isn't explicitly said in the article. When you're getting the SID of the computer account, you need to add a $ to the name or you'll get a translation error. You can also get the sid this way: (get-adcomputer $computeraccount).sid.value (get-adgroup $groupname).sid.value Thanks again!Anonymous
November 06, 2013
The first one for the script. Then you will need to manually add in the second one using SQL management Studio (what I would use - you could probably script this as well.)Anonymous
December 30, 2013
Pingback from Remote Database Creation for App-V 5 Revised | Kirx' BlogAnonymous
February 04, 2014
These are the top Microsoft Support solutions to the most common issues experienced using Microsoft ApplicationAnonymous
February 11, 2015
~ Matthew Keyes | Premier Field Engineer Hi everyone, my name is Matthew Keyes and I’m a Premier FieldAnonymous
April 01, 2015
Thanks for the instructions, they are really helpful. I have just used this procedure to create a database for APP-V SP3, but there is a bug in the scripts that needs attending to for SP3. Follow the Microsoft KB article to amend the scripts -http://support.microsoft.com/en-us/kb/3031340Anonymous
May 22, 2015
I have an addition, I get the following ‘error2’ when running the Permissions.sql script:
Login exists with wrong SID, droping it...
Creating login...
Finished login creation phase.
Remapping orphaned user...
Turns out, SQL uses other syntax to store the SID, so you need to calculate it in another way in order to have the same SID calculated that SQL Server uses. Use the following PowerShell code to calculate your correct SID:
$Account="YourDomainYourAccount"
$NTAccount = New-Object System.Security.Principal.NTAccount($Account)
$SID=$NTAccount.Translate([System.Security.Principal.SecurityIdentifier])
$c = New-Object ‘byte[]’ $sid.BinaryLength
$sid.GetBinaryForm($c, 0)
$c | ForEach-Object -Begin { $NewSid=’0x’ } -Process { $NewSid += «{0:x2}».ToUpper() -f $_ } -End { $NewSid }
This gives the same SID as SQL server uses, and prevents the ‘error’ returned.
Greetz, Ben van ZantenAnonymous
June 30, 2015
~ John Behneman | Senior Support Escalation Engineer Hello everyone, John Behneman here again. I’d likeAnonymous
June 30, 2015
~ John Behneman | Senior Support Escalation Engineer Hello everyone, John Behneman here again. I’d likeAnonymous
September 29, 2015
Here is a list of the top Microsoft Support solutions for the most common issues experienced when youAnonymous
October 03, 2015
BZanten, Your PS code doesn't work, an expression was expected after '('