Posts Tagged ‘staging’
May 1, 2011 | 
Author This week I faced strange issue while working with Staging server. I have a requirement to run some sql scripts whenever the user runs a staging project. I created a sql script and I created a batch file which executes the sql script using sqlcmd command The batch file and sql script are kept in a folder (c:\commercestaging). When I tested the batch file, it worked fine without any errors. I provided the batch file in the staging project properties.
When tested, staging project execution has encounter some errors.
|
Log Name: Application Source: Commerce Server Staging Date: 4/26/2011 9:46:59 AM Event ID: 61223 Task Category: None Level: Error Keywords: Classic User: N/A Computer: Description: Error running the script C:\CommerceStaging\UpdateCounts.bat. Ensure that the script exists and can be located in the path. Event Xml: <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> <System> <Provider Name="Commerce Server Staging" /> <EventID Qualifiers="49152">61223</EventID> <Level>2</Level> <Task>0</Task> <Keywords>0×80000000000000</Keywords> <TimeCreated SystemTime="2011-04-26T16:46:59.000Z" /> <EventRecordID>2487543</EventRecordID> <Channel>Application</Channel> <Computer></Computer>he <Security /> </System> <EventData> <Data>C:\CommerceStaging\UpdateCounts.bat</Data> </EventData> </Event> |
I troubleshooting the issue and at the start, I believed it is problem with the staging project but I was wrong. My batch file has below content.
| sqlcmd -s myserver -i myscript.sql |
This script got executed without error from command prompt but when I ran from staging server, it is failing. I figured out that when I execute the script from staging, staging process is not taking the sql script relative path. I changed the script as follows and the staging projects started working as expected.
| sqlcmd -s myserver -i c:\commercestaging\myscript.sql |
If you like this post, please click on our sponsor advertisement.
Posted in 
Tags: 
We have production site and staging site in different IIS servers. Last week, we have a production issue where we are facing issue in pushing the data from staging server to production server (where it was working before). In the event log, we see below exception.
Failed to export business data for ‘Catalog’ resource for project ‘PushData2Prod’.
Cannot open database "MSCS_Admin" requested by the login.
We figured out that it is happening because the staging service is attempting to connect to MSCS_Admin database using anonymous. After troubleshooting, we figured out that the staging service is running in Network service rather than domain account. Running the service under network account is attempting to the connect to the database anonymously. After making the service run under the domain account the staging service started working fine and we are able to push the data from staging server to production server.
The domain account to the service is normally set when running the configuration wizard after the installation of Commerce Server. It is important to note that you must also re-run the configuration tool after installing Commerce Server service packs . Mostly we might of missed this.
If you like this post, please click on our sponsor advertisement.
We have production site and staging site in different IIS servers. Last week, we have a production issue where we are facing issue in pushing the data from staging server to production server (where it was working before). In the event log, we see below exception.
Failed to export business data for ‘Catalog’ resource for project ‘PushData2Prod’.
Cannot open database "MSCS_Admin" requested by the login.
We figured out that it is happening because the staging service is attempting to connect to MSCS_Admin database using anonymous. After troubleshooting, we figured out that the staging service is running in Network service rather than domain account. Running the service under network account is attempting to the connect to the database anonymously. After making the service run under the domain account the staging service started working fine and we are able to push the data from staging server to production server.
The domain account to the service is normally set when running the configuration wizard after the installation of Commerce Server. It is important to note that you must also re-run the configuration tool after installing Commerce Server service packs . Mostly we might of missed this.
If you like this post, please click on our sponsor advertisement.
In last two articles, we have seen what is commerce server staging and how it works. Check below links for more details.
http://microsoftblog.co.in/commerceserver/introduction-to-commerce-server-staging-2/
http://microsoftblog.co.in/commerceserver/how-commerce-server-staging-works/
In this article, we will see what are the security (access privileges ) consideration we should consider to staging service accounts. Microsoft CS team has provided some recommendations on this. I have seen in couple of implementations, the entire stating is executed on the service account which has admin privileges, which is not recommended. Here are the set of privileges a staging Service account should have.
Database
|
SQL Server Roles
|
|
MSCS_Admin |
admin_reader_role |
|
MSCS_CatalogScratch |
db_datareader, db_datawriter, db_ddladmin |
|
<site>_Marketing |
db_ddladmin, mktg_staging_role |
|
<site>_MarketingLists |
db_datareader |
|
<site>_ProductCatalog |
ctlg_CatalogWriterRole, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, Inventory_ReaderRole, Inventory_WriterRole |
Here are the set of privileges, AD groups (css administrators and css operators accounts) should have -
Database
|
SQL Server Roles
|
|
MSCS_Admin |
db_datareader |
|
MSCS_CatalogScratch |
db_datareader, db_datawriter, db_ddladmin |
|
<site>_ProductCatalog |
ctlg_CatalogReaderRole, Inventory_ReaderRole |
Note: Since staging doesn’t do any operations on <site>_transactions, there is no need of giving access to staging service account to this database.
To make developer life easier, I have created SQL scripts which provides proper access to the service accounts.
|
/* Replace ‘MSCS_Admin’ with your admin DB. Replace ‘MSCS_CatalogScratch’ with your catalog scratch DB. Replace ‘StarterSite_ProductCatalog’ with your catalog db. Replace ‘StarterSite_Profiles’ with your profile db. Replace ‘StarterSite_TransactionConfig’ with your transaction config db. Replace ‘StarterSite_Marketing’ with your marketing db. Replace ‘StarterSite_Marketing_Lists’ with your marketing config db. Replace ‘DOMAIN_NAME\stagingsvc’ with staging service account. Replace ‘DOMAIN_NAME\stagingadmin’ with staging admin account. Replace ‘DOMAIN_NAME\stagingoperator’ with staging operator account. */ PRINT ‘———– UPDATING ADMIN DB ————————–’ USE [MSCS_Admin] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] CREATE USER [DOMAIN_NAME\stagingadmin] FOR LOGIN [DOMAIN_NAME\stagingadmin] WITH DEFAULT_SCHEMA=[dbo] CREATE USER [DOMAIN_NAME\stagingoperator] FOR LOGIN [DOMAIN_NAME\stagingoperator] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘admin_reader_role’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_datareader’ , [DOMAIN_NAME\stagingadmin] EXEC sp_addrolemember ‘db_datareader’ , [DOMAIN_NAME\stagingoperator] PRINT ‘———– UPDATING COMMERCE SCRATCH ————————–’ USE MSCS_CatalogScratch CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_datareader’, [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_datawriter’, [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_ddladmin’, [DOMAIN_NAME\stagingsvc] CREATE USER [DOMAIN_NAME\stagingadmin] FOR LOGIN [DOMAIN_NAME\stagingadmin] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_ddladmin’ , [DOMAIN_NAME\stagingadmin] EXEC sp_addrolemember ‘db_datareader’, [DOMAIN_NAME\stagingadmin] EXEC sp_addrolemember ‘db_datawriter’, [DOMAIN_NAME\stagingadmin] CREATE USER [DOMAIN_NAME\stagingoperator] FOR LOGIN [DOMAIN_NAME\stagingoperator] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_ddladmin’ , [DOMAIN_NAME\stagingoperator] EXEC sp_addrolemember ‘db_datareader’, [DOMAIN_NAME\stagingoperator] EXEC sp_addrolemember ‘db_datawriter’, [DOMAIN_NAME\stagingoperator] PRINT ‘———– UPDATING CATALOG DB ————————–’ USE [StarterSite_ProductCatalog] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘ctlg_CatalogWriterRole’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_datareader’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_datawriter’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_ddladmin’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘db_securityadmin’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘Inventory_ReaderRole’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘Inventory_WriterRole’ , [DOMAIN_NAME\stagingsvc] CREATE USER [DOMAIN_NAME\stagingadmin] FOR LOGIN [DOMAIN_NAME\stagingadmin] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘ctlg_catalogReaderRole’ , [DOMAIN_NAME\stagingadmin] EXEC sp_addrolemember ‘Inventory_ReaderRole’ , [DOMAIN_NAME\stagingadmin] CREATE USER [DOMAIN_NAME\stagingoperator] FOR LOGIN [DOMAIN_NAME\stagingoperator] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘ctlg_catalogReaderRole’ , [DOMAIN_NAME\stagingoperator] EXEC sp_addrolemember ‘Inventory_ReaderRole’ , [DOMAIN_NAME\stagingoperator] PRINT ‘———– UPDATING MARKETING DB ————————–’ USE [StarterSite_Marketing] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_ddladmin’ , [DOMAIN_NAME\stagingsvc] EXEC sp_addrolemember ‘mktg_staging_role’ , [DOMAIN_NAME\stagingsvc] PRINT ‘———– UPDATING MARKETING CONFIG DB ————————–’ USE [StarterSite_Marketing_Lists] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘db_datareader’ , [DOMAIN_NAME\stagingsvc] PRINT ‘———– UPDATING PROFILES DB ————————–’ USE [StarterSite_Profiles] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘Profile_Schema_Manager’ , [DOMAIN_NAME\stagingsvc] PRINT ‘———– UPDATING ORDER CONFIG DB ————————–’ USE [StarterSite_TransactionConfig] CREATE USER [DOMAIN_NAME\stagingsvc] FOR LOGIN [DOMAIN_NAME\stagingsvc] WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ‘Orders_Management’ , [DOMAIN_NAME\stagingsvc] |
If you like this post, please click on our sponsor advertisement.
