Data collection from Citrix Xen server, issue with the connection string

[winvarma]

Hi Forum, Good day!

We are trying to collect Data from the Citrix environment and copied the agent to the server and trying to execute the command via a string to capture the data and push to the staging DB created in the Beacon server as this is a cloud setup.

The Data is being captured in the file where the agent is present and the issue is with the connectivity to the staging DB with the user id which is having DB owner permissions and its the service account of the Beacon where the DB is installed. It throws the below error. We are trying this suggested as per the case we raised for this issue.

C:\Temp\XenAppAgent>FnmpXenAppAgent.exe -d "Data Source=stagingDatabase IP address,port;Initial Catalog=Staging_Xyz;User ID=xyz;Password=***** " -v 9

** Please suggest if the string we are using is valid**

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'xyz'.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Flexera.Compliance.Importer.XenAppAgent.DataHelper.SaveXmlDocToDatabase(XmlDocument p_XenAppXml, String p_DatabaseConnectionString, Int32 p_DatabaseTimeout)
   at Flexera.Compliance.Importer.XenAppAgent.Program.Main(String[] args)
ClientConnectionId:c61fb4d7-aaba-47f5-b47e-accef4ca441e
Error Number:18456,State:1,Class:14

Tried reviewing the blogs related to sql and DB Team tried to crack this but no positive results.

Trying to gather inputs if someone experienced this kind of issue and any leads to overcome this

Regards,

[kclausen]

@winvarma :

1) Verify that the SQL Instance is configured to accept TCP/IP protocol

2) Verify that the standard SQL Server Instance Name and Port are being used.  If not, this information must be put into the connection string

3) Verify that there are no internal proxies/firewalls that are blocking the SQL Server connections/packets

4) Verify that the required SQL Server Client connection utilities are installed

5) On another computer, see if you can connect to this instance using SQL Server Management Studio.  If you can, verify that the same connection names are in your connection string

[winvarma]

Hi Kirk,

Thanks for the inputs, trying to troubleshoot as mentioned in your reply. Do we have to use only a SQL sa account or can we use the windows level account for connecting via a string in this scenario ? What is the best practice while connecting to a staging DB using a string from the end servers to the staging DB while collecting the Citrix inventory and can we mention the string and can you please guide with possible sample strings with arguments if we have to mention any.

Regards,

[kclausen]

The following was used to connect to a SQL Server Express database installed on a beacon.  In this case, a local SQL Server account created in the instance was used to connect.

FnmpXenAppAgent.exe -d "Server=FNMSBEACON01;Database=XenApp_Staging;User Id=XenApp_FNMSStaging;Password=pa$$w0rd1"

If you are attempting to connect with an Active Directory account, you need to provide the domain name such as:

User=Domain\User

[ChrisG]

As opposed to @kclausen's example which connects to SQL Server using SQL authentication, here is an example of a database connection string if you want to connect using Windows Authentication:

"Server=FNMSBEACON01;Database=XenApp_Staging;Integrated Security=true"

[winvarma]

Hi @ChrisG ,

Thanks for guiding with this, will give a try as per the example you suggested and will update further in the community.

Regards,

[winvarma]

Hi Kirk,

The string you suggested worked when we used sa authentication, Do we have a string to collect the data from other Citrix farms as well included in the same task scheduler as arguments and request guidance on the same instead of creating schedules in multiple Citrix servers.

thanks for the guidance.

regards