Scripting Virtual Directory Configuration

Although you would normally use the MMC snap-in to configure SQL Server virtual directories, it's worth pointing out that you can manage every aspect of HTTP publishing programmatically by writing scripts that use the automation objects provided by SQL Server. This strategy is particularly useful if you need to create reusable scripts to install your applications that use SQL Server virtual directory.

SQL Server provides a hierarchical object model of automation objects for virtual directory management. Figure 4-10 shows a graphical representation of the objects.

Figure 4.10 - The SQL Server Virtual Directory Management objects

The SQLVDirControl Object

At the root of the hierarchy is the SQLVDirControl object, which is used to manage an IIS Web site. The SQLVDirControl object provides the Connect method, which you can use to connect to a local or remote IIS Web site. The Connect method accepts two parameters: the server to connect to and the ordinal number of the Web site on the server that you wish to manage. If you don't specify the parameters, they default to the local server and the first Web site, respectively. The object also provides a matching Disconnect method and a collection of SQL Server virtual directories in the specified site through its SQLVDirs property.

The following code sample shows how to connect to the first Web site on a server named webserver and access its collection of virtual directories:

 Dim objSQLIIS Dim objVDirCol Set objSQLIIS = CreateObject("SQLVDir.SQLVDirControl") objSQLIIS.Connect "webserver", "1" Set objVDirCol = objSQLIIS.SQLVDirs 'Process virtual directories  objSQLIIS.Disconnect 

The SQLVDirs Collection Object

You can use the SQLVDirs collection object to access existing virtual directories, create a new virtual directory, or remove an existing virtual directory. This object provides the Item property for accessing a specific virtual directory. Items can be accessed by name or ordinal position. The following code accesses the virtual directory northwinddata:

 Dim objVDir Set objVDir = objVDirCol.Item("northwinddata") 

To create a new virtual directory, you can use the AddVirtualDirectory method of the SQLVDirs collection object this way:

 Dim objVDirNW Set objVDirNW = objVDirCol.AddVirtualDirectory("Nwind2") 

You can also delete virtual directories by using the RemoveVirtualDirectory method of the SQLVDirs collection object.

The SQLVDir Object

The SQLVDir object is used to manage an individual virtual directory. It has a number of properties and methods related to the configuration options available in the New Virtual Directory Properties dialog box described earlier in this chapter. The most commonly used properties of this object are Name, PhysicalPath, ServerName, DatabaseName, SecurityMode, UserName, Password, and AllowFlags.

The Name property is used to configure the name of the virtual directory.

The PhysicalPath property is used to specify the physical path to the directory associated with the virtual directory.

The ServerName property enables you to specify the name of the server that has Microsoft SQL Server 2000 installed.

The DatabaseName property allows you to specify the name of the database on the specified server.

The SecurityMode property enables you to configure the login authentication model used with the virtual directory. You can set it to a numerical value: 1 for trusted server SQL Server security, 2 for trusted server Windows integrated security, 4 for impersonation/delegation using basic authentication to a SQL Server account, and 8 for impersonation/delegation with Windows integrated authentication.

The UserName and Password properties enable you to configure the credentials used to access the database. You need to specify these two properties if you choose either trusted server Windows integrated security or trusted server SQL Server security.

The AllowFlags property is used to configure the types of access allowed through the virtual directory. Numerical values are used to represent the possible types of access as shown in this table:

Value	Type of Access Allowed
1	URL queries
8	Templates
64	XPath queries

The values can be combined to allow multiple types of access. For example, specifying 72 (8 + 64) allows both templates (value 8) and XPath queries (value 64).

The following code sample shows how a virtual directory can be configured using script:

 With objVDirNW     .Name = "Nwind2"     .PhysicalPath = "C:\Nwind2"     .ServerName = "DBServer1"     .DatabaseName = "Northwind"     .SecurityMode = 2     .UserName = "webuseraccount"     .Password = "password"     .AllowFlags = 64 End With 

The VirtualNames Collection Object

You use the VirtualNames collection object to manage the virtual names belonging to a virtual directory. Just as the SQLVDirs collection, the VirtualNames collection object provides an Item property to access existing virtual names and an AddVirtualName method to create new virtual names. The AddVirtualName method includes parameters for the name, type, and location of the virtual name being created. The virtual name type can be specified as 1 for dbobject, 2 for schema, or 4 for template. The following code sample shows a virtual name for templates being added to a virtual directory:

 objVDirNW.VirtualNames.AddVirtualName "TemplateFiles", 4, _     "C:\nwind2\templates" 

The VirtualName Object

You can use the VirtualName object to read or change the properties of an existing virtual name using the Name, Type, and Path properties. For example, you could use the fol lowing code to change the path of the TemplateFiles virtual name created in the previous example:

 Set objVName = objVDirNW.VirtualNames.Item("TemplateFiles") objVName.Path = "D:\nwind2\templates" 

The CreateVRoot.vbs file used to set up the demos on the companion CD contains code to script the creation of a virtual directory.