Solved

SQL Express trusted connections issue

Posted on 2013-06-27
6
412 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
[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
  • 3
  • 3
6 Comments
 
LVL 27

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 27

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 27

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 38
Format Output of Select Statement 2 36
TDE for SQL Web Edition 1 36
COM server issue 2 21
By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
When you try to share a printer , you may receive one of the following error messages. Error message when you use the Add Printer Wizard to share a printer: Windows could not share your printer. Operation could not be completed (Error 0x000006…
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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