How To Find User Database Credentials Prior To Running JMP Script in JMP 16

[#1]

Hello,

I'm running into a problem with sharing the JMP scripts I create across my organization. The problem arises from how the credentials I use to connect to our SQL database are different than the credentials others use to connect to the same database. For example, a query which I can run on my machine to connect to the database "Database" on the server "SQLServer", will look like this: 

Open Database(
	"DSN=SQLServer;Trusted_Connection=Yes;APP=JMP;WSID=MYWSID;DATABASE=Database;",
	"SELECT * FROM [].Table"
)

However, the query which would work on my colleague's machine to query the same table looks like this:

Open Database(
	"DSN=Database;Trusted_Connection=Yes;APP=JMP;WSID=MYWSID;DATABASE=Database;",
	"SELECT * FROM [].Table"
)

I understand this has something to do with how the database connections were initially set up on each user's machine, so there are quite a few different configurations for connecting to the same database within JMP across my organization. This makes it quite difficult to account for several people's differing database credentials whenever I need to create a JMP script which will be used by a large group. 

I've devised a way to check the user's credentials when initially running a script, however this method only works when I know the exact credentials of both myself, and the person who I'm sending the script to for them to use on their machine. How this works is, I start the script by running two simple and quick queries using the two different sets of credentials described above. Depending on which query returns a table, I can then choose from two different expressions containing the actual script I want to run, where each expression contains the same script, just with the different credential sets at the database connections. The problem is, if there's more than two credential sets, then this method still won't work for everyone, and it also becomes difficult to bug fix the script if I have to fix an error in two or more places instead of just one.

Long story short, I want to create one single script which can be used by multiple people who all have different database credentials. Is there any way within JMP 16 to go out and check the user's database credentials to dynamically change the SQL query strings depending on these credentials for each different user who runs the script?

Thanks

[#2 · Accepted solution]

What you are looking for is a DSN-less connection

 that looks like:

"DATABASE=[THE DATABASE NAME];DRIVER={SQL Server Native Client 11.0};SERVER=[THE SERVER NAME]"

In your case, it might look like:

"DRIVER={SQL Server Native Client 11.0};SERVER=[THE SERVER NAME];Trusted_Connection=Yes;APP=JMP;WSID=MYWSID;DATABASE=Database;"

The difference being is that you add the connection information to the connection string rather than using a dsn.

Specifically, the DRIVER= and the SERVER= information

[#3]

That works! Thanks!