?
Solved

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

Posted on 2016-11-03
15
Medium Priority
?
95 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
15 Comments
 
LVL 32

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 32

Expert Comment

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

Give him the proper access :)
0
Looking for the Wi-Fi vendor that's right for you?

We know how difficult it can be to evaluate Wi-Fi vendors, so we created this helpful Wi-Fi Buyer's Guide to help you find the Wi-Fi vendor that's right for your business! Download the guide and get started on our checklist today!

 

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 32

Accepted Solution

by:
Pawan Kumar earned 2000 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 32

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 32

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A new hacking trick has emerged leveraging your own helpdesk or support ticketing tools as an easy way to distribute malware.
An overview of cyber security, cyber crime, and personal protection against hackers. Includes a brief summary of the Equifax breach and why everyone should be aware of it. Other subjects include: how cyber security has failed to advance with technol…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 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