SQL 2012 Permissions Problem

New VM Server - WS 2012 R2 (fully patched), SQL Server R2, fully patched.
I have a VFP exe that uses an ODBC connection to read/write to SQL.  I have used this EXE for years on all levels of SQL.  SQL 2012 - I have some kind of permissions problem.  I can run the same exe on SQL 2008 R2 by simply changing where the ODBC points to.

Scenario - ODBC Connection (32 bit of course for 32 bit VFP) pointed to VM server with SQL Server 2012. Connection test works.  If I start up SQL 2008 Management Studio and connect to the SQL 2012 DE as the same user and password as the ODBC, it works fine and i can add, edit, delete, whatever to my hearts content.  However, when I try to use the VFP using the same ODBC connection, I can read, but when i try to edit, insert, or delete I get the following message:
"[Microsoft]{ODBC SQL Server Driver][SQL SERVER] The Insert permission was denied on the object 'auditdata', database 'mydb', schema 'dbo',"

Interestingly enough, I can use this same ODBC connection with SQL Anywhere's Mobilink to read, write, edit in the SQL 2012 database.

So I am not sure where to look for my problem.  Every permission possible is set to grant access and the Mobilink and SQL Management Studio are quite happy with the access, but not VFP.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The error is generated by SQL Server, so the problem must be in the SQL setup. FoxPro just uses whatever is available...

OK, you did show neither the connection string nor the DSN definition. Also your VFP and ODBC driver version would help slightly.

{ODBC SQL Server Driver] could be the problem. You should try {SQL Native Client}, {SQL Native Client 10.0}, {ODBC Driver 11 for SQL Server} etc.

Define new MS SQL DSN using the SQL Native client and test the connection from VFP. Then use SQL Pass-Through commands to SELECT some data and then try to UPDATE some data. If the error appears then post the full error info obtained by AERROR().

Update note: SQL Native client has know issue when retrieving varchar(max) data.
DonnaOsburnAuthor Commented:
VFP 9.0 SP 2
On Windows 7 box pointing to server
ODBC is a System DSN
Microsoft SQL Server ODBC Driver Version 06.01.7601
Data Source Name: pmeesql
Server: WS2012R2STD

I put the VFP exe on Server itself and get the same error.
ODBC is a System DSN
Microsoft SQL Server Native Client Version 11.00.5058
Data Source Name: pmeesql
Data Source Description: PriceMaster Plus
Server: WS2012R2STD
Use Integrated Security: No

Foxpro Code example

pnconnect = SQLCONNECT("ODBCNameHere","SQL User name here","SQL Password Here")

      SQLSETPROP(pnconnect,"Asynchronous", .F.)

      lcstring =       "Select * from analystCD"

      lcstring =       "INSERT INTO [databasename].[dbo].[analystcd] ([fcanalcd],[fcanaldesc],[flchange],[fldelete],[flstatus],[fllock],[fccreate],[ftcreate]) VALUES ('donna3', 'donnadesc3', 0, 0, 1, 0, 'master', GETDATE())"

this VFP Code works fine run as is above and lets me insert data with no errors.
Let me dig deep inside the EXE code to find how it makes its connection (although i thought it was the same as this code.
Great!  It sounds like different user privileges used by your app.

Also 'auditdata' could point to a table which is populated by some trigger which also could use different privileges...

You may use the SQL Server Profiler to check what's going on when the application is running.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

DonnaOsburnAuthor Commented:
Okay - so after much stepping through code I have the following Aerror array.

1 - 1526
2 - Connectivity error:
3 - "[Microsoft][ODBC SQL Server Driver][SQL Server]The INSERT permission was denied on the object 'auditdata', database 'pmplus', schema 'dbo'."
4 - 42000 (from the SQLEXECUTE command
5 - 229
6 - 1
7 - blank

If I take the same line of code that the SQL execute is running (an Insert statement in this case) and drop it in the SQL Management Studio query window it executes immediately.  Which tells me its not a syntax issue.  This exact same code all works on SQL 2008.  Could I have set something up improperly when I configured SQL 2012 which would cause this denial?  I configured it as a default instance.  Remember that I can attach and read anything, I just can't edit, update, or delete through VFP with the ODBC connection.
It cannot be syntax issue if the error message states "Permission denied". And other rows of the error state just confirm it.

So important is the user under which the command is issued and its privileges.

Does your application use exactly same user and password as you use in SSMS?
DonnaOsburnAuthor Commented:
Yes, the SSMS is connected from the Win 7 machine to the WS2012 machine with the exact same SQL user name and password.  I even tried using the SA user name and password and i get the same insert permission error.

So copy the SQLExec command from the fox code to the SSMS and it works correctly, but using SQLEXEC in VFP fails with the permissions error.
DonnaOsburnAuthor Commented:
The Win 7 machine has SQL 2008 R2 installed on it.  If I simply change the ODBC to point to the SQL 2008 database, the VFP EXE works fine.  I backed up the DB from the SQL 2008 machine and restored it on SQL 2012 machine (and dropped the users and added them again to fix all the pointers) Other than that, I did not change anything about the Database.  The user is given every possible permission.

What can i do to try and drill down and find the problem? I have all the VFP Source code.
"The user is given every possible permission." ... Are you sure no permissions are denied?

So what could you do next... Are you sure the user has

Create a new user in SQL Server 2012 and GRANT him all permissions to auditdata table. You may look here how to do it: http://www.mssqltips.com/sqlservertip/1138/giving-and-removing-permissions-in-sql-server/

Now try to use this user from your application or from VFP directly.

Remember there is no difference between SSMS and your app access to the remote database. So if SSMS can insert data to the remote database then the app must do it without problem under exactly same user.

BTW, you wrote "this VFP Code works fine run as is above and lets me insert data with no errors." in your post ID: 40406799. What was the difference between your code and the code used by your app?
DonnaOsburnAuthor Commented:
Well, I agree with you wholeheartedly!  It should work.  I went down your line of thought and I have created three different users and tried them all with the same results - they all work in SSMS but do not work from the VFP exe.  The users are configured at DB_Owner and should have all permissions to everything.

So - what changed from SQL 2008 R2 to SQL 2012 R2 that broke Visual FoxPro?
Then the last untested thing is different ODBC driver...

So was it working from VFP.EXE or not? (pointing to your post ID: 40406799)
DonnaOsburnAuthor Commented:
I have a compiled Visual Foxpro 9 EXE - I can point it using ODBC to any SQL Server in my office for testing purposes.

If I take this same compiled EXE and change the ODBC to SQL 2000, SQL 2005 or SQL 2008 it runs correctly.  Now that I am pointing the same EXE at SQL 2012 - it is broken.   No code has changed on the VFP side.

If I step through the source to see where the error pops up, I grab the SQL Statement it is trying to run from the VFP source code, drop that in SSMS and it runs correctly.
DonnaOsburnAuthor Commented:
I have stated above which exact drivers I am using.  I would appreciate any thoughts as to what other driver to try.  Bear in mind that VFP is 32 bit and requires the 32 bit driver.
All SQL drivers are coming in both 32 and 64 bit versions.

Execute ODBCAD32.EXE  from  your Windows\SysWOW64\  folder and look what SQL Native clients you have available (on Drivers tab). Then use one of them. I would start with SQL Server Native Client 11.
DonnaOsburnAuthor Commented:
odbcad32.exe in syswow64 is the one i am using.  I have tried
SQL Server Native Client 11 (just retried it again) i even downloaded drivers and installed them again.
SQL Server Native Client 10
and SQL Server 6.01.xxx

same results on all three.
DonnaOsburnAuthor Commented:
Ran and ODBC Trace and got this.  If i copy the insert into line and drop it in SMSS it works correctly and adds the record.

vfp9            1530-1664	ENTER SQLExecDirect 
		HSTMT               0x009A11D8
		UCHAR *             0x0431FB90 [      -3] "INSERT INTO AuditData ( FIMENU, FCAUDITTYP, FCAUDITNT, FLSTATUS, FLLOCK, FCCREATE, FTCREATE ) VALUES ( 28, 'Edit', 'Analyst Code: AZAVALA,', 1, 1, 'steve', '10/29/2014 14:33:37' )\ 0"
		SDWORD                    -3

vfp9            1530-1664	EXIT  SQLExecDirect  with return code -1 (SQL_ERROR)
		HSTMT               0x009A11D8
		UCHAR *             0x0431FB90 [      -3] "INSERT INTO AuditData ( FIMENU, FCAUDITTYP, FCAUDITNT, FLSTATUS, FLLOCK, FCCREATE, FTCREATE ) VALUES ( 28, 'Edit', 'Analyst Code: AZAVALA,', 1, 1, 'steve', '10/29/2014 14:33:37' )\ 0"
		SDWORD                    -3

		DIAG [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT permission was denied on the object 'auditdata', database 'pmplus', schema 'dbo'. (229) 

Open in new window

The problem is in permissions for sure...

Try following in SSMS:
- Right Click the table
- Properties
- Permissions
- (if necessary) add the user or role
- click on the user/role
- permission insert, put a check-mark in the 'grant' box.

Test the insert command from VFP IDE.
DonnaOsburnAuthor Commented:
No change whatsoever.  Same error.  Could this be a windows security issue thing and not a sql thing at all?
No, this is purely SQL Server issue. The error is raised in SQL Server and it points to SQL Server access rights.

Can you please confirm again both the SSMS and VFP 9 IDE is installed on the local client machine (your Windows 7) and the SQL Server 2012 is installed on the remote server?
One more attempt: Can you create SP and try the INSERT inside this SP?
DonnaOsburnAuthor Commented:
It appears that calling VFP code that uses stored procedures works with no errors.  It's only when I issue the SQLEXEC with a direct sql command that causes the problem and even then only for writes, not reads.  I'm still trying to get the trace dump out of it to verify. but it did change the database from VFP.
The INSERT command is compiled when you are calling the SP.

If you call SQLEXEC then it uses Dynamic SQL at the SQL Server side. And dynamic SQL needs different permissions to write the data on SQL 2012. It is simply more dangerous command which is sensitive to SQL injection etc. so it has different security context...

For that reason I've been asking to create a new set of EXPLICIT permissions for SQL user on given SQL table used in VFP app (see the post ID: 40411972 above).

If you did it correctly then the only reason why it still does not work is different user used by the application or some kind of impersonation during the app run etc. The impersonation would not take into effect in VFP IDE but you are saying SQL Pass-through does not work from VFP IDE so the only reason are permissions on SQL Server...

One thing you could test is SQLPREPARE() function which could avoid the dynamic execution:

lnResult = SQLPREPARE(lnHandle, "INSERT INTO .....")
IF lnResult > 0
  lnResult = SQLEXEC(lnHandle)

I am not sure but it could compile the command at SQL side.
Olaf DoschkeSoftware DeveloperCommented:
In all this discussion I don't find with which user you connect and with which type of authentication, Windows authentication or SQL Server authentication. Just you could connect doesn't mean you have all permissions. The old SQL Server 2008 must have all permissions set up correctly and you didn't move that to the new Server.

As you still have both servers look into authentication mode, look into security and permissions. Look deep and intensive. Also into options allowing dynamic sql at all.

Bye, Olaf.
DonnaOsburnAuthor Commented:
SQL authentication only. Yes all same users and same permissions in SQL 2012 as exist in working copy of SQL  2008. I even tried using SA login and get same error.
Olaf DoschkeSoftware DeveloperCommented:
If even SA can't do something it's a sure sign you need to activate some feature. But an INSERT into a table should work. Are you sure you connect corretly to connect as SA? What is your connection string?

Bye, Olaf.
Olaf, this is surely some security setting...

INSERT does not work when connected remotely only. INSERT works in all other cases - local connection, remote SP call...

One of the recommendations was to define explicit access rights for the user on given table. If this did not help then I am lost. (Other possibility is incorrect explicit access rights definition...)
Olaf DoschkeSoftware DeveloperCommented:
I ask about connection string defined for the DSN, because if Trusted_Connection=Yes Usr and Pwd specified in the same connection are not used for the login, you're then connecting with the NT account and windows authentication (SSPI) and don't work as SA. And that might be the simple difference between working from VFP and from SSMS. You're logging in different and you're not logged in as the user you think you are logged in.

So make the connection from VFP, then start SSMS and execute sp_who there. You'll see who's logged in to which database(s) and you'll see whether there are users with loginame being DOMAIN/NTUser and not sa or another internal user.

Bye, Olaf.
DonnaOsburnAuthor Commented:
This is the ODBC Connection String
Microsoft SQL Server Native Client Version 11.00.2100

Data Source Name: pmeesql
Data Source Description: PMEE
Server: WS2012R2STD
Use Integrated Security: No
Database: pmplus
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

Testing the data source says it test successfully.

This is on a windows 7 box pointing to server on WS 2012 box.  

I think you may be on the right track because when i check the sp_who - it is logged in with a user that i did not use in the ODBC - it is logged in as the one who has read only access.  Now i need to see how it got that user when nothing is set up to use that user.  More code stepping :).
So you have r/o users...

You wrote the user and password is provided by the application already:
pnconnect = SQLCONNECT("ODBCNameHere","SQL User name here","SQL Password Here")

So I supposed the user has full access rights and the user IS used by your app to connect the database...

Good work Olaf.
Olaf DoschkeSoftware DeveloperCommented:
As you have Use Integrated Security: No The user/password provided in SQLConnect are not working, you are using Windows authentication.

Integrated Security is the SQL Server authentication eg for SA user, but if that is set to NO in the ODBC connection the user is the windows domain user and not a SQL Server user, even if you use the cUserID and cPassword parameters of SQLCONNECT.

So that's what I would change and test first.

Bye, Olaf.
That's almost true, just opposite :-)

Integrated security means Windows security integrated into SQL Server aka Trusted Connection.

Once it is set then the connection string ignores user and password.
Olaf DoschkeSoftware DeveloperCommented:
Yes, Pavel is right. Googling it I see it is correct as is. Sorry.

Still you have some other login as you thought you have, so you're on the right track finding out what's wrong. The values you provide in SQLCONNECT are not correct or you SQLDISCONNECT and SQLCONNECT with other credentials elsewhere in your VFP app.

Bye, Olaf.
DonnaOsburnAuthor Commented:
Update - I found in the code where it was hard coded to use the user name and password for the user who has read only privileges... which makes sense to protect SQL.  Then it used that connection to verify the users name and password and delete that connection and create a new one with the correct SQL user configuration for the user logging into the application.

What I haven't been able to track down is why it is not resetting the connection to what the access is that the user has.  Technically, I don't think you can read the user name AND password from the DSN - you have to start somewhere so that is why the user with read only permission is hard coded in system.

So i am much closer.   Olaf put me in the right direction when he told me to check who i was actually logged in as using sp_who.  It wasn't who I was expecting to be logged in as.
You can read the username from Data source but the password is not saved and you have to provide it in the SQLCONNECT() call or the login dialog appears or the error is returned. The password box in DSN definition is used just for the Data source testing.

But maybe some previous Windows versions saved the password into DSN...

It is not bad approach to login with restricted rights, provide some authentication data and as a response receive unrestricted user credentials which can be generated randomly for each single login attempt.

OK, if you don't create shared connection and if you call SQLDISCONNECT(0) then it should not be reused  and the new session should use the new username/password.
Olaf DoschkeSoftware DeveloperCommented:
>Technically, I don't think you can read the user name AND password from the DSN.
Why would anyone do so. When you'd use windows authentication there would be a constant connection string with "Trusted_Connection=yes" for all users and the'd get their access rights defined by their windows account or user group membership, that's a much easier and the recommended authentication mode for SQL Server anyway.

Bye, Olaf.
DonnaOsburnAuthor Commented:
Okay - so stepping through the VFP code....  I get caught in an infinite loop where it cannot commit the transaction on the SQL 2012 box.  Do i need different properties or additional properties from what i have used before to make this work?
I have used the connection string like above.

           lcSqlStr = "SELECT @@TRANCOUNT nTrans"
        lnRetVal = .EXEC( SET("DATASESSION"), lcSqlStr, '', .T., .T. )
        IF sqlresult.nTrans > 0
          *** We need to close one here
          SQLCOMMIT( This.nHandle )
          .nTranCount = 0

Open in new window

lnRetVal is always .t.  and sqlresult.nTrans is always 1
Olaf DoschkeSoftware DeveloperCommented:
What has this to do with your connection problem? You're in the middle of a new problem.
This suggests you're using manual transactions via SQLSETPROP(This.nHandle,"TRANSACTION",2)

You shouldn't and even if you do you'd need to SQLCOMMIT() different handles, not many times the same.
@@TRANC'OUNT not decrementing more than a certain amount means there are other manual transactions started from other connections.

Bye, Olaf.

Edit: As far as I read online books @@TRANCOUNT is the count of transactions in the same connection, so that's good. But still there will perhaps be transactions started by executing "BEGIN TRANSACTION", which you don't close via SQLCOMMIT.
DonnaOsburnAuthor Commented:
What this has to do with my connection problem is because I cannot "commit" what the read only user has done(the user that the initial connection is made to SQL with), I cannot close the handle.  I prefer to only have one handle open at a time.
Olaf DoschkeSoftware DeveloperCommented:
SQLEXEC(This.nHandle,"ROLLBACK") should rollback all open transactions.

Bye, Olaf.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DonnaOsburnAuthor Commented:
I do not understand, but here is where I am now...
If I use the 32 bit ODBC and use the SQL Server 6.0.1 driver - everything works correctly. It will write the commits and not get stuck in the infinite loop.
If I use any of the SQL Server Native client (still using the c:\windows\syswow64\odbcad32.exe) - 2005, 2009, or 2011 - i get stuck in an infinite look around the commit statement.

So I really think this is a driver issue and not a Foxpro problem.  I am going to mark this as closed and if I run into problems again, I will create a new question.

Olaf and pcelba - i thank you for your insights and help.
BTW, what returns  SQLCOMMIT( This.nHandle ) ?
Are you committing some data but  the result is not important for you?

And I agree, r/o data should not be committed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.