Can't use SQL Server Authentication via ODBC to run VFP application

It is sometimes better to have one SQL account for the application and handle user logins at application level.

You are not mentioning whether the connection is established or not after providing correct credentials….

If you are able to connect from VFP application then you have to provide the credentials in SQLCONNECT() call or the whole connection string in SQLSTRINGCONNECT() call. Once e.g. the pwd is missing the dialog appears. Of course, you may set the connection properties to return error immediately without the login dialog popup.

If the connection is refused then this is known issue after Windows 10 update.  You should check two things:

1) Allow Named Pipes connection in SQL Server properties
2) Allow SMB1 in Windows 10 functions.

I concur on both comments and can add more details to the issue.  

We are migrating the  SQL databases for a few clients from within their domains to server hosted outside of their domain.  Windows authentication has always worked within their domain by creating the app user group and adding everyone to it.  Windows authentication also still works if I have the username and password in the SQL database on the remote server, but I don't want to try to maintain all the usernames and passwords involved on the remote SQL server and I don't believe a user group will work outside of their domain.  

I can successfully connect to the SQL database using SQL Server Authentication via ODBC and the SQL server management tool using a username and password added to the SQL server.  However, when I run the VFP app using ODBC to connect to the remote server it will run without issue using Windows authentication, but when ODBC is set to SQL Server Authentication the VFP app seems to prompt for logging in to the SQL server with every query or something like that.

Named Pipes is allowed and we're not using Windows 10.

I am looking into what a File DSN might do for this situation.

This is issue has become a high priority today.  I posted another question under SQL re first time File DSN setup.  Any additional comments you might have will be appreciated.  Thanks!

So this means you are able to connect.

The File DSN does not bring any new value against connection string or System/User DSN.

Did you check whether the connection is closed after certain time?
Did you use SQL Profiler to trace the traffic and connected apps?
Are you able to check the connection periodically in your app?
Did you try to connect and query data from VFP Command window?

What ODBC driver version do you use?
Did you try different driver?
What connection properties do you use?
What commands are you using to query the data?
What kind of SQL Server access does the app use? If it is just SPT then the login dialog does not appear when the connection does not exist and SQLEXEC returns an error.

Answering all your questions, the best I can for now.  Let me know if you need more or if I didn't understand the question.

 Did you check whether the connection is closed after certain time? - It is not.  Remember it works with Windows Authentication, but not SQL Server Authentication.

 Did you use SQL Profiler to trace the traffic and connected apps?  I have not done this yet.

 Are you able to check the connection periodically in your app?  I could, but the prompts to reconnect to SQL are immediate and too often to make this an effective solution.

 Did you try to connect and query data from VFP Command window?  Yes.  If the ODBC connection is set to Windows Authentication it connects without issue.  If the connection is set to SQL Server Authentication I get the SQL Server Login popup, but then I can query the tables.  Sample connection and query:
handle=SQLCONNECT('CES SQL')
set classlib to classes\wscs
oLib=createobj('connection')
oLib.connect()  && AT THIS POINT I GET THE SQL SERVER POPUP ONLY IF USING SQL SERVER AUTHENTICATION
lcCmd='SELECT * FROM Customers'
lnSQL=SQLEXEC(handle, lcCmd, 'tTemp')

 What ODBC driver version do you use?  32 Bit.  6.3.9600.17415
 Did you try different driver?  No, but same driver from different systems has the same effect, works with Windows, but not SQL Server authentication.
 What connection properties do you use?  I attached a screen shot of all the settings.
 What commands are you using to query the data?  There are sql queries, stored procedure calls, etc.  That doesn't seem to make a difference to the repeated prompts to login to the SQL Server.
 What kind of SQL Server access does the app use? If it is just SPT then the login dialog does not appear when the connection does not exist and SQLEXEC returns an error.  I think the example above answers this question.  Let me know if not.
ODBC-Settings.png

handle=SQLCONNECT('CES SQL')
set classlib to classes\wscs
oLib=createobj('connection')
oLib.connect()  && AT THIS POINT I GET THE SQL SERVER POPUP...

Select allOpen in new window

The last step seems wrong, you already have your connection with handle = SQLCONNECT('CES SQL')

What is your 'connection' class actually doing in the connect method? Notice: There is no native VFP base class called connection, so this must be a class by you or a framework class you use. Set s breakpoint there and single step into the code. Since this isn't having any parameter this must use a constant as DSN name or connection string, I don't think you're using this right.

Why are you double connecting, is this about two databases? Your final SQLEXEC is using the handle variable you set with the first line, using the DSN name only.

SQLConnect() can use a connection name of a connection object in a DBC, then you need to look in there if you store the right parameters including password, and it's not about a DSN at all, neither a system nor a user nor a file DSN. Actually, when you do SQLCONNECT() alone you can see what connections are available, and file DSNs are not among them, to use a file DSN you need a connection string specifying a filename, eg SQLSTRINGCONNECT("filedsn=c:\dsn\DsnFile.dsn"), this needs to know path and file name, not just a DSN name, that's not enough.

So I see you sabotaging yourself not really knowing what the connection class does and not using your file dsn.

Bye, Olaf.

The ODBC dialog accepts a password. BUT: it is not stored in user or system DSN's. You need to use a file DSN, where you add the passwords manually by using the PWD parameter.

This is by design.

In addition to Olaf's post: does the connect object have a ConnectionString property or even a Password property? Assuming that this connect class is built similar like ADO or DAO classes in other systems, you should be able to set the password before calling connect().

btw:

Windows authentication also still works if I have the username and password in the SQL database on the remote server [..]

This is not Windows authentication. Windows authentication is using an AD to manage users and groups and use those in SQL Server.

Windows authentication can work even without AD but we may omit it because it works for you.

Olaf pointed to the incorrect (or unclear at least) wscs class usage. The code snippet you've posted does not need it.

handle=SQLCONNECT('CES SQL')    ….. this command should establish the connection and you have to check the "handle" value to ensure the connection was established. Then you may use the "handle" for all other SPT function calls. Note: This command may also display login dialog when credentials are not stored in the app.

SQLSTRINGCONNECT() should also work here.

Once the connection is established the you may query data:

lcCmd='SELECT * FROM Customers'
lnSQL=SQLEXEC(handle, lcCmd, 'tTemp')

And after this command you should check the output value and process possible errors (wen the lnSQL < 0).  Of course, the output cursor existence is enough in some cases but you should ensure the cursor deletion before the SQLEXEC command.

I am also interested to know What is your 'connection' class actually doing in the app?  It is possible to have more connections in one app but you should really know their purpose.

The ODBC DSN definition is correct. I am just repeating: DSN definition does not store the password so login dialog must appear when you don't provide the pwd some other way.

Maybe this will help clear up the confusion on how this application is using ODBC to connect to the SQL database.

In the application there is class called "wscs" with an object called "connection" with the procedure called "Connect" with only the following code:
(Note the property This.DSN='CES SQL')

            * Establish connection based on DSN
            IF EMPTY(this.dsn)
                  This.connected=.F.
                  RETURN
            ENDIF

            =SQLSETPROP(0,"PacketSize",8192)
            This.handle=SQLCONNECT(this.dsn)
            IF This.handle<0
                  This.connected=.F.
                  RETURN
            ENDIF
            This.connected=.T.      

Once this connection is established the application defers to it for SQL calls like this simple example:

            lcCmd='SELECT * FROM Customers'
            lnSQL=SQLEXEC(This.Handle, lcCmd, 'tTemp')

I should have more clearly defined that I was responding to the question,"Did you try to connect and query data from VFP Command window?" with the following snipet. If I want to use ODBC to connect to the SQL database from the command prompt I do so like this:
            set classlib to classes\wscs
            oLib=createobj('connection')
            oLib.connect()  && AT THIS POINT I GET THE SQL SERVER POPUP...

Yes, it must display the login dialog because you are not providing username and password in the SQLCONNECT():

This.handle=SQLCONNECT(this.dsn)

DSN can store username but not the password (as stated several times). The password entered in ODBC Administrator when configuring DSN is used for DSN test and initial settings from SQL Server only.

So you have two options:  Store the password in the application (encrypted, of course, which is still unsecure because we are in FoxPro) or let users to enter it manually (which is also unsecure if the whole app uses just one SQL account).

More secure options are: Create SQL account for each user or use trusted connection or use one password for the whole app and implement security at the application level.

As always guys, thanks so much!  I was able to piece your suggestions together and am up and running.  

So Windows Authentication has worked all these years not because the ODBC driver is saving the password, but because the users are included in the domain group that has the proper permissions on the SQL server.

If we're going to use SQL Server Authentication we need to find a way to store and include a password when connecting to the SQL database or have the users enter the password.  The latter may be complicated because the users are promtped any time the data container is connecting to the SQL database.

I've created a CESSQL.dsn file and given it a password line:
      PWD=SamplePassword

This successfully tests the dsn file and it's saved password in the VFP command window:
      oHandle=SQLSTRINGCONNECT("filedsn=c:\DSN\CESSQL.dsn")
      lcCmd='SELECT * FROM Customers'
      lnSQL=SQLEXEC(oHandle, lcCmd, 'tTemp')

I have updated the afore mentioned library wscs.connection.connect() to the following:
      *This.handle=SQLCONNECT(this.dsn)
      This.handle=SQLSTRINGCONNECT("filedsn=c:\DSN\CESSQL.dsn")

In the apps DBC I modified the connection from the system dsn to the file dsn and the app runs successfully without the user having to login at all.  It was the DBC connections that was causing the multiple prompts for logging back into the SQL database and it was this behavior that truly had me stumped.  I could understand having to log in when first opening the app, but why the repeated login prompts after that?  

I believe this issue is resolved, but cannot real world test until after hours.  I will add additional comments if I run into other issues.  Let me know if you have concerns with this solution.  

Thanks again.

You've resolved this okay. Another solution is using the user&password parameters of SQLCONNECT(dsn, user, pass) and you can include usr=sa pwd=sapassword inside a connection string, for example, using the sa user or any other SQL Server user like an application specific user, when you want to allow connections from users outside the domain controller.

And yes, within a company LAN for company users only the windows authentication is simplest, as it's given by the connection string option Trusted_connection=yes and then the login is handled via Windows account security, which is fine, secure, and leaves out to use any credentials in INIs or source code. Users can then be granted permissions on the basis of windows user groups, in the simplest case you maintain a windows user group as a login in SQL Server and grant that all permissions, then allowing a user in just means adding him to that group in normal windows user administration, which is likely what you already did.

Just FYI, with an OleDB provider a similar option to IDBCs "Trusted_Connection=yes" is "Integrated Security=true;" or "Integrated Security=SSPI;" and the difference between true and SSPI is only historically, true used NTML and SSPI used Kerberos authentication on the level of SQL Server delegating the authentication to Windows security.

Overall it's wrong to say your users didn't log in, they didn't need to enter credentials again, like it's typically enough to log in once into your client and then every resource you use requiring your authentication is handling that with your SID and token the system maintains for your session, so you don't need to confirm again and again for each file or share or whatever other resource requiring your identity to check permissions.

Bye, Olaf.

Follow up note, this only works for me if in the VFP dbc I right click the background, select Connections, modify the connection, changing from Data Source to Connection String, click the ellipsis and navigate to the DSN file previously created via the ODBC driver.  Doing this pulls in the entire contents of the DSN file, including the saved password.  

I didn't get enough testing done tonight to verify this will work using a DSN file shared on the network.  I will add to this when ready.

Just a note: To leave an open password in file DSN sounds like a big vulnerability issue to me. It is much better to save the encrypted pwd inside the application or in some app external file.

this only works for me if in the VFP dbc I right click the background, select Connections, modify the connection, changing from Data Source to Connection String, click the ellipsis and navigate to the DSN file previously created via the ODBC driver

VFPs connection designer/dialog will do that, yes, but as I stated you can keep this external specifying the connection string filedsn=fullpathfilenameofa.dsn, eg with its network share UNC path.

The problem always will be as Pavel says that password is stored in clear text and as your EXE runs in the session of its user it only has its permission, which means the users need read access of the DSN file, the only protection you have is they don't know where that file is. It can be monitored which files your EXE reads, though. In the end, password protection always is a topic just deferring the need to store some secret. Store the password somewhere else encrypted and compose the DSN file with the decryption of it on the fly just for the connection then purge it from memory. If you do that, the new secret is the key for en/decryption. The only way to keep a password or key secret outside of any executable or file is to let it be entered.

That seems a short circuit with no solution. Well, you can encrypt the password and give them the key to decrypt it, but not let them know that this key decrypts the SQL Server password and where that encrypted key is stored. The only disadvantage is, that's not a believable application login if everybody needs to enter the same key and they can't choose their own login password.

I have seen passwords stored in INI files in clear text. The reason this can be ok is the computer itself already is login restricted and people are bound by working contracts, too. If you have medicinal patient data or financial data, that becomes a problem.

For the moment I think you'd just be happy if this connection works at all, but if you'd be interested to make this secure you could go for https://msdn.microsoft.com/en-us/library/ms867086.aspx Cryptography API.

Applying that to a FoxPro EXE, which could be decompiled makes that a very secure lock on a cardboard box, as VFP has no SecureString class and is always vulnerable to a memory dump revealing any variable contents of any secrets, too. That's a good reason to let passwords look like binary data or garbage, especially not using words at all, rather a random letter/number/special character combination, which would obfuscate it at least, even if it's found in decrypted cleartext, it could still be overlooked in several MB of dumpfile.

Bye, Olaf.

Switching from Windows authentication to a File DSN allowed me to redirect multiple users in multiple environments from onsite SQL databases to offsite in three easy steps, add a File DSN to the local servers, modify the SQLCONNECTION() line in VFP and modify the VFP DBC connection to the File DSN. No interaction on the users systems was required and Windows authentication is not needed.  There are security concerns with this new configuration and we are addressing those following the suggestions above.  Thanks again to everyone.