Solved

SQL VB connection works in one PC and doesn't in another

Posted on 2016-11-03
15
71 Views
Last Modified: 2016-11-09
We have an application running within a windows server, with the following vb6 code to connect to it's sql 2008 instance:

 cnn.Open "Provider=SQLOLEDB; " & _
 "Initial Catalog=" & dbname & "; " & _
 "Data Source=" & sqlserver & "; " & _
 "integrated security=SSPI; persist security info=True;"

Open in new window

(Which makes the sql connection 'Provider=SQLOLEDB; Initial Catalog=DBNAME; Data Source=SQLINSTANCE; integrated security=SSPI; persist security info=True;')

The problem,
If the user is administrator, all works great, if the user is non-admin, doesn't connect and displays "Error number: -214721783".

We went into sql to the problem table and granted permission to select/delete/insert/update and still same error.  Note that the non-admin user has access to all other tables with the database, it's only with this table; thus, sql permission of some sort.

Please advice.
0
Comment
Question by:rayluvs
  • 9
  • 5
15 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41871950
Can this user connect to SQL Server using SSMS and run SELECT command ?
0
 

Author Comment

by:rayluvs
ID: 41871961
Just checked and logged into the server with the non-admin user name, ran SSMS, and no; cannot even log in.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41871966
Thats the issue. Please fix it !!

Give him the proper access :)
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:rayluvs
ID: 41871968
Please provide the steps.

Thank you.
0
 

Author Comment

by:rayluvs
ID: 41871976
FYY:

We went into Sql 2008 Instance >> security >> login and check the following:

- there is a password for the user in General,
- in server role he is 'public',
- in User Mapping he has access to the correct databases,
- in Securables is empty,
- in Status, permission granted and login enabled

What are we missing?
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41871984
In User Mapping --> Database role member ship for

db_owner

, you will get this at the bottom of the screen.
0
 

Author Comment

by:rayluvs
ID: 41871996
Done.  Same error as stated in the question.  Also tried to get the user logged in and can't.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41871998
Pls post the error?
0
 

Author Comment

by:rayluvs
ID: 41872002
Error number: -214721783
Login failed for user SqlInstanceName\UserName
0
 

Author Comment

by:rayluvs
ID: 41872116
FYI,

Please note that user has access to all other tables within the database, it's only with a specific table.  The user as all users has been working with the database for more than 5 years without giving them owner rights to the database.

The problem is with this specific table that is used  thru a vb6 apps we have.  Again, all apps the users uses to access and work with the database, where the table was created recently, has no problem.

Since the problem is with this table only, what can de causing this problem?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41872241
Okay , So what they are storing in that table.  Sensitive info. ?
0
 

Author Comment

by:rayluvs
ID: 41872372
It's a new table storing customer specific info; not that sensitive data.
0
 

Author Comment

by:rayluvs
ID: 41873267
We have gone over and over this problem.   We have looked into what we think is everything and still searching; the only conclusion that we can come up with is window-user-permissions on table.

The curios thing,
  • When Windows administrator logs into Windows Server and logs in the application of said database, he has no problem with the new table created (a vb6 apps that uses it).
  • If the Admin logs into Windows Server and then logs to the application with a non-admin username (an SQL username that the apps uses), again, he has no problem with the our vb6 apps.
  • However, if the non-admin logs into Windows Server and logs in the application of said database, they have no problem, but as soon as they run the vb6 apps they get Error number: -214721783 'Login failed for user SqlInstanceName\UserName'
  • Finally, if the non-admin user logs into Windows Server and then logs in the application of said database with "SA" (SQL user), and run the vb6 apps they get the Error

So conclusion,
  • The problem has to be the permissions Windows User created in the Windows Server.
  • Why?
Because the same vb6 apps that accesses with the new table functions excellent when the user Administrator logs in the server and it same exact apps doesn't work when a non-administrator logs into the server.

Hope this info helps, help us.

UPDATE:
We created a new data base and a new table, pointed the apps there and same problem; a non-admin running the apps displays the error:

Error number: -214721783
Login failed for user SqlInstanceName\UserName

Hence, it's something in the SQL instance where a non-admin user cannot access new DB or Tables.
0
 

Author Closing Comment

by:rayluvs
ID: 41881485
Besides In User Mapping --> Database role member >> db_owner, we also granted permission.

Thanx!  Your entry gave us the lead!
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

685 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