Updating SMP Database With Custom Workflow Components

[Migration User]

I have written a custom workflow that creates consumable and fixed catalog items. In this workflow I have numerous components I've written myself, and these components include the necessary delete and insert steps to ensure the proper table structure and relationships are maintained.

The workflow operates fine in debugger (due to heightened privileges). I have updated my connection strings for these custom components to use the SMP administrator account (the one used during installation) that subsequently has sysadmin rights to the database.

I am able to test the connection fine in the integration library, however, when I run my program it fails when trying to login with this system account and password. In a ditch effort I created a custom App Pool on the server and entered the SMP administrator account credentials into it. No luck, though, as I am still getting a login error.

Any ideas on how best to create custom workflow components that insert and update the SMP database?

Login failed for user '<system account?>'.

Set Manufacturer For Resource

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at SetManufacturerForResource.SqlQuery.SetManufacturerForResource.Run(IData data) at LogicBase.Core.ExecutionEngine.SinglePathProcessComponentExecutionDelegate.Execute(IData data, IOrchestrationComponent comp, String& outputPath, IExecutionEngine engine, TLExecutionContext context) at LogicBase.Core.ExecutionEngine.AbstractExecutionEngine.RunComponent(TLExecutionContext context, IData data, IOrchestrationComponent comp)

[Migration User]

Look at the app pool the workflow is deployed under. Associate the app pool with an identity that has privileges to the database. You can check the registry on the SMP I believe to determine the SQL user identity used by the SMP

[Migration User]

reecardo:

Thanks for the quick response. I see I am not the only one working around the holidays.

I did create a separate App Pool and associate it with my custom workflow project. I restarted IIS and tested the workflow, however, I get the same result--error logging in using the system account.

So, currently I have things set up like this:

• The Connection String explicitly uses the system account and password, and I tested it can connect successfully in my integration library. I am also using the following settings: Integrated Security=True;Pooling=True
• I created a separate Classic App Pool and associated it with the system account and password
• I validated the system account did in fact have sysadmin privileges against the database, which it does.

What else could I be missing?

[africo]

If you've done some testing with the project, you may have some old connection string info stuck in the publishing page.  Check this page during publishing:

Ensure that the Project Value has loaded.  Just click the "Use Project Value" link and it'll load the proper string in for you, if it's not already correct.

[Migration User]

Greate suggestion africo.

I confirmed the publishing settings and they were indeed different--it was missing the Integrated Security portion of the connection string. I changed this and published the workflow, however, it is still failing on the same security issue--not able to connect using the system account.

[africo]

I'm happy to put another set of eyes on it if you want to do a webex to check it.  Send me a PM if you want to take 15 mins to have a look.

[bcason]

Check the context that 'Symantec Workflow Server' is running as. It looks like it is running as 'Local System'. Change this to the same identity as your IIS Application Pool and ensure that the account has sufficient privileges to your SMP database.