VFP SQL Connection Failing When Compiled into the .EXE

[stanlyn]

Hi,

This SPT code works fine while within vfp9, however it asks for credentials after being compiled into an exe. The connection string is intentionally embedded into the exe. What needs to be done for it to work in an exe without any prompts as this runs headless?

lcDSNLess = "Driver={SQL Server}; Server=zzzDAS; UId=sa; Pwd=passw"
gnConnectionHandle = Sqlstringconnect((lcDSNLess), .T.)

Thanks for your help,
Stanley Barnett

 

[Nigel Gomm]

stanlyn,

try

SQLSETPROP(0,"DispLogin",3)

hth

n

 

[GriffMG]

Perhaps, and I'm only guessing, your VFP9 session is elevated as Administrator and those credentials match the SQL server, but they do not when you are using the connection string
in your example.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[stanlyn]

Hi Griff,

I would thing credentials would be the same, regardless of where they are running. The credentials in the connection string works within SSMS on my workstation, VFP on the workstation when running the main prg. It fails on my workstation when running the compiled exe from my workstation. Makes no sense to me, as a connection is a connection string, right? The SQL Server is on the LAN as well as my workstation.

Thanks,
Stanley

 

[GriffMG]

think windows authentication

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[stanlyn]

Nigel,

I've added your code, which works only when running elevated, otherwise fails with "Cannot connect to data source: (Driver=SQL Server..." and after clearing that OK only dialog I get a Connection handle is invalid" dialog with cancel, ignore and help buttons. This runs perfectly from the main program from VFP's gui.

So, how can I get it to run elevated without presenting the elevation prompt because it is running as a service? I do control every thing on the machine (Win2016 server).

Would it help if the exe was code signed? I am trying to avoid the signed certificates route since both SQL Server and the vfp app's host server is on the same lan and I have full control all. They too are also part of the same DC.

Any suggestions on how to configure?

Thanks, Stanley

 

[stanlyn]

Hi Griff,

So changing the connection string to a Integrated Security = trus setup without the uid and pwd stuff? Both machines on the domain are trusted as well.

Thanks, Stanley

 

[stanlyn]

Hi Griff,

And that will suppress the UAC dialogs?

Thanks, Stanley

 

[vernpace]

stanlyn,

You need to dump the UID and PWD and use Windows Authenication - btw, what you are using now is a security risk. Here is what we use for our cursor adapters:

For local servers: ConnectString = "Driver={SQL Server Native Client 11.0};Server=(local)\<<Server Name>>;Database=<<Database Name>>;Trusted_Connection=Yes"
Just replace <<Server Name>> with your server (instance) name and <<Database Name>> with your database name - without the brackets, of course. The server (instance) name is show below as TESTSITE:

Note the Native Client syntax - we switched in 2012 because it is faster, more versatile, and easier to use. For more information:

https://docs.microsoft.com/en-us/sq...ql-server-native-client?view=sql-server-ver15

Also, forget about code signing. It has nothing to do with SQL Server authenication. Here is information on code signing:

https://en.wikipedia.org/wiki/Code_signing

 

[stanlyn]

Hi, vernpace,

Not having any luck yet after last 2 hours at this... It may be an odbc driver issue. I've tried version 17.7 and 13.1 native driver installs. Both the SQL Server and the computer that is hosting the VFP app are on Server 2016 64b machines. 32 bit versions of odbc will not install onto those machines. I am trying to use the dsnless approach, so running the client tool in WOW64 is of no use, as dsnless strings are setup in code. I can only get it working using the uid and password style using driver = "SQL Server", not client.

I have also set perms on sql server with access rights as well. What is the best version of odbc to use where all machines are 64 bit?

I will work with any suggestion, and thanks.
Stanley

 

[tomk3]

Hello,

first of all I strongly recommend to switch to windows auth., too.
driver : we are using odbc native client 11.0 without problem

Another idea : Is it possible that it is a firewall issue allowing vfp ide but not the compiled exe ?

Regards
tom

 

[vernpace]

stanlyn,

Most SQL Server installations are 64 bit - 32 bit versions died long ago. As such, 64 bit ODBC drivers are required. Note that the calling application (eg VFP) can be 32 bit - it does not have to be 64 bit. Also, it is not necessary to hassle with installing ODBC drivers when you can use the SQL Server Native Client (See the syntax in the above connection string) To find out which version to use, go to to the SQL Server Configuration Manager:

You should see the version installed - In our case, we specify {SQL Server Native Client 11.0} in the connection string. Also, make sure that the Shared Memory protocol is enabled:

Now try something: Go into SSMS and try to connect using Windows Authenication (as shown in my original post). Does it work? If it does, then you should have no problem using the connection string I suggested unless your Login Server Roles are restrictive. You should verify:

 

[stanlyn]

Hi tomk3,

>> Another idea : Is it possible that it is a firewall issue allowing vfp ide but not the compiled exe ?

Bingo, I disabled the firewall on the SQL Server and it works... Enabling it causes it to fail... Now, wonder what exception do I need to make? The machine that is hosting SQL Server also has a copy of VFP9 installed. As far as I know, the ports are the same.

Any idea what the exception looks like?

Thanks, Stanley

 

[stanlyn]

Hi,

After further testing, the area of the firewall that is blocking is "Domain Network" settings (top section). With it disabled and the other two sections enabled, it works.

Because it's in the domain section, do I have a domain/machine trust issue, exe permission issue or ???

Thanks,
Stanley

 

[stanlyn]

Hi vernpace,

lcDSNLess = "Driver={SQL Server Native Client 11.0};Server=(local)\SlaZZZ;Database=MyDatabase;Trusted_Connection=Yes"
	gnConnectionHandle = Sqlstringconnect((lcDSNLess), .T.)
?gnConnectionHandle

This returns a -1 (fails)

Note that you are using the word "local". So not sure what its context is, I'll explain a bit... The vfp app is hosted on Server2 and SQL Server is hosted on Server1, and both are in the same lan and domain. The term "local" just looks out of place here. I would think term "local" would refer to itself, (same machine).

So with this info, do I need to change your line to something else?

I've also reviewed the config manager and roles and all look good there. In this particular instance, I only care about the 32bit section, correct?

Thanks, Stanley

 

[stanlyn]

Hi vernpace,

	lcDSNLess = "Driver={SQL Server Native Client 11.0};Server=SlaZZZ;UId=sa;Pwd=zxzxzx;Database=MyDatabase"

Is also failing with a -1...

However,

	lcDSNLess = "Driver={SQL Server};Server=SlaZZZ;UId=sa;Pwd=zxzxzx;Database=MyDatabase"

Works with a 1...

Just more info,

Thanks, Stanley

 

[stanlyn]

Hi vernpace,

lcDSNLess = "Driver={SQL Server};Server=(local)\SlaZZZ;Database=MyDatabase;Trusted_Connection=Yes"

This caused a 20 second delay and generates this dialog, then results in a -1.

This is the first time I've seen this dialog, so I'm looking up error 17, 53, 8001, and 1000...

Thanks,
Stanley

 

[stanlyn]

Hi vernpace,

lcDSNLess = "Driver={SQL Server};Server=SlaZZZ;Database=MyDatabase;Trusted_Connection=Yes"

Is now working. What is strange, is I haven't made any changes. Is there a delay somewhere?

However,

lcDSNLess = "Driver={SQL Server Native Client 11.0};Server=SlaZZZ;Database=MyDatabase;Trusted_Connection=Yes"

Still fails... Only difference is "SQL Server" vs "SQL Server Native Client 11.0"

Thanks,
Stanley

 

[vernpace]

stanlyn,

Yes, the (local)\ section is used for an SQL Server instalation on your local computer and should not be used if you are accessing SQL Server on another server. The good news is that you are now using Windows Authenication with:

lcDSNLess = "Driver={SQL Server};Server=SlaZZZ;Database=MyDatabase;Trusted_Connection=Yes"

It's odd that SQL Server Native Client 11.0 is not working. This automatically installs with SQL Server 2012 and up...

 

[stanlyn]

vernpace,

I still have something wrong as it take 21 seconds to create the connection via the trusted connections method, whereas specifying a uid and pw is instant.

lnStart = SECONDS()

Do 'CreateConnection'

TEXT to m.lcSqlStr textmerge NOSHOW PRETEXT 15
	SELECT * FROM [Deedroom].[dbo].[WebUser]
ENDTEXT

lnSqlResults = SQLExec(gnConnectionHandle, m.lcSqlStr)

Messagebox('Seconds: ' + STR(SECONDS() - lnStart))

Here is CreateConnection

Procedure CreateConnection
SQLSETPROP(0, "ConnectTimeOut", 40)
SQLSETPROP(0, "IdleTimeout", 0)		&& minutes

lcDSNLess = "Driver={SQL Server};Server=SlaZZZ;Database=MyDatabase;Trusted_Connection=Yes"
	gnConnectionHandle = Sqlstringconnect((lcDSNLess), .T.)
	
     If gnConnectionHandle < 0
          Messagebox('Cannot connect to data source: ' + Left(lcDSNLess, 20) + '...', ;
	     16, 'SQL Connection Error', 10000)
          Return
     Endif
     Return
Endproc

Any ideas?