Change SQL Srvr 2005 Authentication modes
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alpesh Patel
Yes, In property of Server in SSMS, change authentication mode. 
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!!
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!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
link to guide through create ODBC connection
http://www.truthsolutions.com/sql/odbc/creating_a_new_odbc_dsn.htm
http://www.truthsolutions.com/sql/odbc/creating_a_new_odbc_dsn.htm
ASKER
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
+ 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you wont find user SA in database user, there will be dbo
I assume u are trying to find it in
DatabaseEngine<InstanceNam
whereas it is in
DatabaseEngine<InstanceNam
image attached for ref.
sql-sec-2005.png
I assume u are trying to find it in
DatabaseEngine<InstanceNam
whereas it is in
DatabaseEngine<InstanceNam
image attached for ref.
sql-sec-2005.png
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.
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.
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.
"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.
ASKER
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.
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.
ASKER
perhaps my description of the issue was insufficient to deliver an answer