Hi,
My name is Somesh Sahu and I am facing an issue while trying to connect GP company database using logged-in user credentials.
I have developed a new window using GP Add-in and it is accessed from Customer Management -> Additional menu. There is a new User table which stores some additional information for Customers. There is a lookup button on this new window for selecting a Customer. When a normal User say 'abc' clicks this look-up button, it opens the 'Customer & Prospects' window but when User selects a Customer and click select button, it displays a message 'Login failed for user 'abc'' as this new Custom table is queried to fetch the data. This message also appears while accessing this new window as there are some 'Select' queries on form load. I have read the articles on this problem and seems that Normal user is not able to connect SQL Server because the password is encrypted in GP which is used in the connection string to connect to database.
dbConn.ConnectionString = "Password=" + userPwd + ";Persist Security Info=True;User ID=" + userID + ";Initial Catalog=" + databaseName + ";Data Source=" + serverName + ";Connection Timeout=" + connectionTimeout;
where userID = Dynamics.Globals.UserId.Value and userPwd = Dynamics.Globals.SqlPassword.Value
This problem does not occur when User is SQL Server System Administrator i.e. 'sa' as GP does not store its encrypted password.
As this new window will be accessed by normal users, they should be able to connect SQL Server and access the new tables
Please let me know the resolution of this issue.
Thanks in advance.
Regards,
Somesh Sahu