Solved

SQL Express trusted connections issue

Posted on 2013-06-27
6
392 Views
Last Modified: 2013-07-01
We have a problem with an old application using local accounts and trusted connections to SQL.

Old setup that works fine:
•Computer SERV has SQL express installed on it
•VB app from another computer needs to connect to SERV to access SQL - in total two VB apps access the SQL in SERV: the one on COMPUTER1 and the one on SERV
•both computers are installed in a truck, part of our mobile fleet, so no local domain controller and intermittent access to Internet
•This is how it works presently: VB application running on COMPUTER1(Windows XP) under a local account(COMPUTER1\dispense)connects to SQL Express 2005 instance running on another computer SERV(Windows XP). In SQL server the access is granted to user SERV\dispense and to make it work it looks like in the key manager on COMPUTER1 the credentials for SERV\dispense are stored so it would appear COMPUTER1 is impersonating the local account defined on SERV.

We need to move this app onto Windows 7 and I need advice on the best way to do this. If we simply setup the app in the same way and cache the credentials of the foreign account like on Windows Xp(above) we get an error: Login failed for user ". The user is not associated with a trusted SQL Server connection.

It seems this, impersonation, works differently under Windows 7 - how can we fix this and/or how should we be doing this?

Thanks in advance
0
Comment
Question by:ee-gd
  • 3
  • 3
6 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39282090
How does the connection string look in the vb apps?

Have you tried to give  COMPUTER1\dispense user access to the database? Can the app from SERV box access the SQL instance?
0
 

Author Comment

by:ee-gd
ID: 39282125
the database is on computer SERV so COMPUTER1\dispense is not a local account on SERV and cannot be added in SQL - if you try to add it: it says Windows NT user or group not found because it cannot find the account COMPUTER1\dispense locally on SERV

as for the connection string itself - i don't have that, but will post when i obtain it.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39282190
You can create a SQL login on the SQL server and give it a password, and then use that user/password in the app connection string. In this case it will not be a trusted connection anymore but you will be able to access the SQL server if it is accessible on the local network for from COMPUTER!. You can use it even locally.

Trusted connection is there mostly for the network domain context so if you don't have domain then is easier to use users with SQL authentication.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:ee-gd
ID: 39282471
I suggested using SQL logins before, but was told that it is hard and time consuming to implement because all their DLLs use trusted auth etc. Admittedly not a simple app and with many plug-ins, but it is not inconceivable that a connection string can't be changed - unless the trusted stuff is hard coded in too many places and not documented properly etc.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39282772
The way things look in some way that info will have to be changed unless really there is a setting in windows 7 to replicate the situation in the old OS. It will be a problem if for security reasons that would not be possible anymore in W7.
0
 

Author Comment

by:ee-gd
ID: 39289556
it turned out we were trying to connect from Windows 7 to Windows Xp machine in the lab and will be switching to sql logins as you suggested.

For reference a chap from Microsoft in a TechNet forum confirmed that as long as passwords are identical we should be able to connect usign trusted authentication and we can, but only when both machines are either running WinXp or Win7. Mixing the two did not work, but it works fine when both machines are running Windows 7.


If COMPUTER1 has an account COMPUTER1\dispense with password XYZ123ab and if SERV has an account SERV\dispense  with password XYZ123ab and if the SQL Server on SERV has a Windows Authentication login for SERV\dispense then you should be able to connect using Windows Authentication. The account names and passwords on both computers must match. This hasn't changed since Windows XP. In fact I just checked it connecting from Windows Server 2012 to Windows 8.1. Still works.
--------------------------------------------------------------------------------

Rick Byham, Microsoft
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
On some Windows 7 (SP1) computers, Windows Update becomes super slow even the computer is reasonably fast.  There's one solution that seemed to have worked well for me (after trying a few other suggested solutions).
This Micro Tutorial will teach you how to the overview of Microsoft Security Essentials. This is a free anti-virus software that guards your PC against viruses, spyware, worms, and other malicious software. This will be demonstrated using Windows…
Viewers will learn how the fundamental information of how to create a table.

863 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

24 Experts available now in Live!

Get 1:1 Help Now