Database Publishing Service used with Database Publishing Wizard

In the previous post (Useful SQL Database Transfer Tool) I mentioned Service that can be installed on a a server and receive requests directly from the Publishing Wizard.

This is an open source project. The compiled version can be downloaded from Microsoft SQL Server Database Publishing Services 1.1

I installed it using the defaults.

I had to change the IIS as follows to get it to run:

1. Change the Virtual directory properties so that it is an application
   
2. Change to basic authentication
   
   You can ignore the warning about passwords sent in free form because the service only accepts https: connections

Browse to https://localhost/SqlPublish/Publish/Service.asmx and you see these WEB Service methods:

• BeginPublish
• CancelPublish
• EndPublish
• GetServiceOptions
• PublishData
• PublishScript

I tested a transfer using a command line like:
SqlPubWiz publish -S QWERTY\SQLEXPRESS -d Northwind -RW https://localhost/SqlPublish/Publish/Service.asmx -RWU John -RWP hidden -RS QWERTY\SQLEXPRESS -RD Northwind2 -RU John -RP hidden

This failed because the certificate has an external name:

Error: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

I changed the URL in the command above to: https://testing.company.com/SqlPublish/Publish/Service.asmx

I now get a .NET error

System.Data.SqlClient.SqlException: Login failed for user 'John'. The user is not associated with a trusted SQL Server connection.

I create a nw login with server authentication (a no-no normally)

I also changed the server properties do that it also allowed SQL Server authentication and restarted the SQL Server

I then had to give 'myself' permission to do stuff

Then it couldn't find Nortwind2 so I foolishly made "master" the DB and it created the tables in master.

It seems you have to create the DB first (who knows how you do this remotely?)

In summary:
SqlPubWiz publish -S KENTSTREET09\SQLEXPRESS -d Northwind -RW https://direct.148kentstreet.com/SqlPublish/Publish/Service.asmx -RWU John -RWP hidden -RS KENTSTREET09\SQLEXPRESS -RD master -RU myself -RP hidden