Solved

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

Posted on 2016-11-03
15
64 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

One of the biggest threats facing all high-value targets are APT's.  These threats include sophisticated tactics that "often starts with mapping human organization and collecting intelligence on employees, who are nowadays a weaker link than network…
One of the biggest threats in the cyber realm pertains to advanced persistent threats (APTs). This paper is a compare and contrast of Russian and Chinese APT's.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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