Using active directory to connect to a database without having to set up an ODBC

[mdav2]

I have a SQL server database that has been set up to be accessed through active directory.
To access it I have created a SQL Server ODBC DSN using the wizard in the ODBC manager and can connect with the following code:

STORE SQLSTRINGCONNECT('dsn=saw_live') TO gnConnHandle
IF gnConnHandle < 0
   = MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
   = MESSAGEBOX('Connection made', 48, 'SQL Connect Message')
   = SQLDISCONNECT(gnConnHandle)
ENDIF

I want to avoid the setup of a ODBC DSN and was wondering if I can do a SQLCONNECTSTRING passing the ID, server etc.
The main reason is that this is then easier to deploy.
I've tried this by adding the UID and SERVER to the string conect but no joy.
Has anyone gone through this pain and has a solution I would appreciate it.

Thanks in advance,


Mark Davies
Warwickshire County Council

 

[Mike Lewis]

Mark,

If I've understood your question correctly, you want to substitute 'dsn=saw_live' in your SQLSTRINGCONNECT() with the actual connection string. So you are asking what value you should use as the string. Is that right?

If so, one approach would be for you to use the ODBC applet, as you have already done (what you are referring to as the "wizard"), but this time specify that it should a File DSN, rather than a System or User DSN. When you do this, the contents of the DSN will be saved in a .DSN file, which is a plain text file. It will be saved in your DSN directory (if you're not sure where that it, search your drive for *.DSN).

Once you've done that, you can open the file in a text editor. You then add a semi-colon at the end of each each, then concatenate the lines. So you end up with one long string, with each parameter terminated by a semi-colon. That is the string that you need to pass to SQLSTRINGCONNECT(). You don't need to retain the text file.

If I've misunderstood the scenario, my apologies.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mdav2]

Cheers Mike, I have looked at that and just tried adding the elements that made sense. I'll try it with all the settings and see if that works.

Mark Davies
Warwickshire County Council

 

[mdav2]

I stripped out some of the unnecessary stuff from the file DSN and got it down to just the following.
It was the trusted_connection that made the difference.
The parts I removed were the WSID, ID and DESCRIPTION

lcDSNLess = "DRIVER=SQL Server;UID=myusername;Trusted_Connection=Yes;SERVER=myservername"
gnConnHandle = SQLSTRINGCONNECT(m.lcDSNLess)

IF gnConnHandle < 0
   = MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
   = MESSAGEBOX('Connection made', 48, 'SQL Connect Message')
   = SQLDISCONNECT(gnConnHandle)
ENDIF

Thanks,

Mark.

Mark Davies
Warwickshire County Council

 

[Mike Lewis]

Glad to see you've got it working, Mark. You're right about TrustedConnection. I've made the same mistake myself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy