Solved

SQL 2012 Permissions Problem

Posted on 2014-10-27
40
360 Views
Last Modified: 2014-11-10
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.

Help!
Thanks.
0
Comment
Question by:DonnaOsburn
  • 17
  • 15
  • 8
40 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 40406697
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40406799
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.)
      SQLSETPROP(pnconnect,"BatchMode",.T.)
      SQLSETPROP(pnconnect,"ConnectTimeOut",0)
      SQLSETPROP(pnconnect,"DispLogin",3)
      SQLSETPROP(pnconnect,"DispWarnings",.F.)
      SQLSETPROP(pnconnect,"IdleTimeout",0)
      SQLSETPROP(pnconnect,"QueryTimeOut",0)
      SQLSETPROP(pnconnect,"Transactions",1)

      lcstring =       "Select * from analystCD"

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

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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40406846
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40409353
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40410325
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?
0
 

Author Comment

by:DonnaOsburn
ID: 40410787
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40410813
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40411419
"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?
0
 

Author Comment

by:DonnaOsburn
ID: 40411442
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?
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40411452
Then the last untested thing is different ODBC driver...

So was it working from VFP.EXE or not? (pointing to your post ID: 40406799)
0
 

Author Comment

by:DonnaOsburn
ID: 40411457
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40411461
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40411574
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40411598
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40411664
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

0
 
LVL 41

Expert Comment

by:pcelba
ID: 40411972
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40412002
No change whatsoever.  Same error.  Could this be a windows security issue thing and not a sql thing at all?
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40412059
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?
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40412096
One more attempt: Can you create SP and try the INSERT inside this SP?
0
 

Author Comment

by:DonnaOsburn
ID: 40414350
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:pcelba
ID: 40414458
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)
ENDIF

I am not sure but it could compile the command at SQL side.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40417916
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40418057
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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40418881
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40419308
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...)
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40419513
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40419622
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 :).
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40420010
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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40420404
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40420506
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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40420526
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40424489
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.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 200 total points
ID: 40425043
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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40425581
>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.
0
 

Author Comment

by:DonnaOsburn
ID: 40432926
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"
DO WHILE .T.
        lnRetVal = .EXEC( SET("DATASESSION"), lcSqlStr, '', .T., .T. )
        IF sqlresult.nTrans > 0
          *** We need to close one here
          SQLCOMMIT( This.nHandle )
        ELSE
          .nTranCount = 0
          EXIT
        ENDIF
      ENDDO

Open in new window


lnRetVal is always .t.  and sqlresult.nTrans is always 1
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40432983
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.
0
 

Author Comment

by:DonnaOsburn
ID: 40433011
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.
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 300 total points
ID: 40433047
SQLEXEC(This.nHandle,"ROLLBACK") should rollback all open transactions.

Bye, Olaf.
0
 

Author Comment

by:DonnaOsburn
ID: 40433372
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40433707
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
INSERT INTO SELECT JOIN THING 2 24
c# code 19 58
sql calculate averages 18 24
SQL Date Retrival 7 26
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now