"Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication" when accessing SQL PI using SQL Server PI repository (4339565)

WORKAROUND

When the user attempts to add a new MSSQL PI agent using a SQL Server PI repository they are prompted to enter the following information,

parpDatabase=
parpType=MSSQL
parpHost=
parpUsername=

However these parameters are not exposed. In order to change this a groovy script is necessary.

The variables Username and Password should be changed in the script and then run this script in the Foglight script console. 

This script is only to change the username password for SQL Server and SSAS agents.

/*
* The below variables are available to scripts:
* scope              - the selected topology object in parent page, null if it does not represent
* specificTimeRange  - the current specific time range from time range selector 
* functionHelper     - the FunctionHelper instance for getting other information, see the Web Component Framework Documentation 
* log                - a Log instance
* server             - the ServerAnchor instance to retrieve public service, see the Service Layer Documentation
* out                - the output stream
*/
/**
 * print specified primary ASPs for specified agents
**/	
//Input the userName and password
userName = "sa";
password = "mypassword";


log = org.apache.commons.logging.LogFactory.getLog("script." + functionHelper.getFunctionId());
configService = server["ConfigService"];
agentService = server["AgentService"];

//MSSQL agent related ASPs
def db_mssql_agents = agentService.findByAdapterAndType("FglAM","DB_SQL_Server"); 
log.info("DB_SQL_Server number =>" + db_mssql_agents.size());

def db_mssql_spi_repository_agents = agentService.findByAdapterAndType("FglAM","DB_SQL_Server_SPI_Repository"); 
log.info("DB_SQL_Server_SPI_Repository number =>" + db_mssql_spi_repository_agents.size());

piEnableAsp = "paecEnable";
userNameAsp = "parcStorageUsername";
passwordAsp = "parcStoragePassword";
changeCredentails(db_mssql_agents,true, piEnableAsp,userNameAsp,passwordAsp,userName,password);

piEnableAsp = "spirpEnable";
userNameAsp = "spirpUsername";
passwordAsp = "spirpPassword";
changeCredentails(db_mssql_spi_repository_agents,true, piEnableAsp,userNameAsp,passwordAsp,userName,password);


//SSAS agent related ASPs
def db_ssas_agents = agentService.findByAdapterAndType("FglAM","SSAS_Agent"); 
log.info("SSAS_Agent number =>" + db_ssas_agents.size());

def db_ssas_spi_repository_agents = agentService.findByAdapterAndType("FglAM","SSAS_SPI_Repository"); 
log.info("SSAS_SPI_Repository number =>" + db_ssas_spi_repository_agents.size());

piEnableAsp = "";
userNameAsp = "ssasSpiCommonStorageUsername";
passwordAsp = "ssasSpiCommonStoragePassword";
changeCredentails(db_ssas_agents,false, piEnableAsp,userNameAsp,passwordAsp,userName,password);

piEnableAsp = "spirpEnable";
userNameAsp = "spirpUsername";
passwordAsp = "spirpPassword";
changeCredentails(db_ssas_spi_repository_agents,true,piEnableAsp,userNameAsp,passwordAsp,userName,password);
//SPI Repository ASPs
def SPIRepository_agents = agentService.findByAdapterAndType("FglAM","SPIRepository"); 
log.info("SPIRepository number =>" + SPIRepository_agents.size());

piEnableAsp = "parpEnable";
userNameAsp = "parpUsername";
passwordAsp = "parpPassword";

changeCredentails(SPIRepository_agents,true,piEnableAsp,userNameAsp,passwordAsp,userName,password);

return "success";


def changeCredentails(agents,isCheckEnable, piEnableAsp,userNameAsp,passwordAsp,userName,password) {
	agents?.each {agent->
					def namespace = agent.getAgentNamespace();
					def hostname = agent.getHostname();
					def agentType = agent.getTypeId();
					def agentId = agent.getId();
					def agentName = agent.getName();
					def primaryASP = configService.getAgentInstancePrimaryAsp(namespace, agentType, agentId);
					
					if (primaryASP) {
						if (isCheckEnable) {
							if (primaryASP.getBoolean(piEnableAsp)) {
								setASP(agent, primaryASP, userNameAsp,passwordAsp,userName,password);
							}
						} else {
							setASP(agent, primaryASP, userNameAsp,passwordAsp,userName,password);
						}
					}
	}
}

def setASP(agent, primaryASP, userNameAsp,passwordAsp,userName,password) {
	def oldUserName = primaryASP.getString(userNameAsp);
	def oldPassword = primaryASP.getString(passwordAsp);
	primaryASP.setValueByString(userNameAsp, userName);
	primaryASP.setValueByString(passwordAsp, password);
	configService.saveConfig(primaryASP);
	log.info("Credentials for agent "+ agent.getName() +" changed: userName from " +  oldUserName + " to " + userName +"; password from " + oldPassword + " to " + password);
}

STATUS

Enhancement PIFOG-756 was added in the 5.9.7.10 and higher releases of the SQL Server cartridge to include a 'Manage SQL PI Repository' panel to change the credential for SQL Server PI repository more easily.