Solved

Change SQL Srvr 2005 Authentication modes

Posted on 2010-11-21
12
413 Views
Last Modified: 2012-05-10
I have a SQL 2005 db that the software vendor has set up with SQL Authentication.  The System DSN on the cllent machines uses the SA account to connect to the DB.

When I tried to explain to the software vendor that we should use Windows Authentication, they had no idea what I was talking about.  

Is there a simple trustworthy way to change the security on the DB to use Windows Authentication instead of SQL.
0
Comment
Question by:mbkitmgr
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 7

Accepted Solution

by:
macentrap earned 125 total points
ID: 34185807
in SQL 2005, if its using SQL mode its already using mixed mode ( SQL server and Windows).

There are only two options in properties:
Windows authentication and mixed mode.

to use windows authentication you would have to create users in security group
SQL-Security.png
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34185816
Yes, In property of Server in SSMS,  change authentication mode. PAge
0
 
LVL 7

Expert Comment

by:macentrap
ID: 34185817
misinterpretd the question:

you would like to change the system DSN on client machine

just select option for windows authentication and create that user as part of security -> logins

it should work!!
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 7

Assisted Solution

by:macentrap
macentrap earned 125 total points
ID: 34185830
from server it looks fine... how about security logins ... are the domain users' created.

or do you need help from System DSN on client machine
0
 
LVL 7

Expert Comment

by:macentrap
ID: 34185858
link to guide through create ODBC connection

http://www.truthsolutions.com/sql/odbc/creating_a_new_odbc_dsn.htm
0
 
LVL 5

Author Comment

by:mbkitmgr
ID: 34185875
I guess I need to clarify the issue.

+ The System DSN on the existing client machines are using the SA credential, which isnt an AD account
+ When I try to configure System DSN on a new machine with the SA credential, it fails with an error that the username and password are incorrect
+ When I check the Logins under the Instance, there is no SA acc listed
+ When I check the Database properties SA is listed as the owner
+ I cant find a user SA in the database Users either.

I had hoped by switching to Windows Authentication we could do away with the SA paradox
0
 
LVL 7

Assisted Solution

by:macentrap
macentrap earned 125 total points
ID: 34185943
SA is default sql server account (sql adminstrator)
This wont be part of AD
System dsn got sql method checked to connect at database

When sytem dsn is created it ask for how you would like to conect to database
I.e. Windows nt or sql
Thts where u got sql


Regarding setup on new machine wht error do you get?

Does your existing client work with initial setup of SA?


0
 
LVL 7

Expert Comment

by:macentrap
ID: 34186610
you wont find user SA in database user, there will be dbo

I assume u are trying to find it in
DatabaseEngine<InstanceName> -> databases<databaseName> -> security -> logins

whereas it is in

DatabaseEngine<InstanceName>- > security -> logins

image attached for ref.




sql-sec-2005.png
0
 
LVL 2

Expert Comment

by:marat-oz
ID: 34193472
If your Vendors application is not win app there is no way you can use win authentication; otherwise create win login, give it necessary server/DB access privileges and ask Vendor to change login account for application.
If all your users and apps are using Windows you can set Win authentication mode on server otherwise keep it mixed.
sa account has administrative rights on server and this could be security issue.
If your Vendor app isn't Windows app you can create SQL Server account with less access privileges than sa but with all necessary privileges for Vendors app and ask your Vendor to change app login account to newly created one.
0
 
LVL 2

Expert Comment

by:marat-oz
ID: 34193697
PS
"ask Vendor to change login account for application."
"ask your Vendor to change app login account to newly created one."

Actually, I believe you can do it yourself, all you need is to ask vendor what Server/DB access privileges this account needs.
0
 
LVL 5

Author Comment

by:mbkitmgr
ID: 34296208
Hi Guys,

An Update on the issue - the initial problem with the creation of the OBDC Sys DSN has been resolved.  It seems the OEM image was the problem.  I deleted the OEM install of Win7, re-installed Win7 from the OEM media kit and "hey presto" the ODBC connection now works.  

The app was Lawware. I have also expressed to the vendor that under the current settings any user with Excel can query the db and obtain user-names and passwords because the DSN is using the sa login.  Sadly some vendors don't consider the risk this poses.

So for now the problem has receded, and I have left the vendor to make their own decision on how Lawware's ODBC connector authenticates.
0
 
LVL 5

Author Closing Comment

by:mbkitmgr
ID: 34296247
perhaps my description of the issue was insufficient to deliver an answer
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Data via Excel--performance issues 2 55
Parsing the XML data to SQL Server 4 66
ms sql + top 1 for each customer 3 51
Grid querry results 41 78
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

778 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