Solved

SQL Express trusted connections issue

Posted on 2013-06-27
6
384 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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