SQL Authenication

I have a windows 2008 server running SQL 2005 using sql authentication.

Users from a client software need to authenticate using sql to run reports

i have to allow SQL authentication in the registry on the server  - but as the server is part of a domain I can only find the domain or built in users. How to I add the SQL users?
James DanahayDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
you are not clear on how the information is stored by the client. How the client is coded. Does the client access the DB without issues?

You can either setup an odbc connection that the client uses, or grant users read rights if it can do integrated authentication using MS windows.

Check with the client software vendor.
Are you creating reports on your own?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jose TorresCertified Database AdministratorCommented:
First make sure that you are using mixed mode authentication "SQL Server and Windows Authentication mode" and not "Windows Authentication mode"
SELECT 
	CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
		WHEN 1 THEN 'Windows Authentication mode'   
		WHEN 0 THEN 'SQL Server and Windows Authentication mode'   
	END

Open in new window

If it's not set to mixed mode refer to the following MS article
How to: Change Server Authentication Mode
If it's already set to mixed mode or after setting to mixed mode you will need to create a SQL Login for the application to use.  Refer to the following MS article
CREATE LOGIN (Transact-SQL)

Updated:
If the client application needs to access SSRS and its not using active directory then refer to this MS article
Implementing a Security Extension
James DanahayDirectorAuthor Commented:
Hi Jose

The sql is in mixed mode and the sa and other users are already configured correctly. As the users need to run reports they need to connect via the sql user. For this to happen the vendor has told me that I need to give the sql user access to the HKEY_LOCAL_MACHINE & HKEY_CURRENT_USER in the registry. The problem I face is how do I give the sql user this access in the registry as I can only see local and domain users from the registry
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jose TorresCertified Database AdministratorCommented:
SQL login accounts are internal to SQL server only. These accounts can not be granted access to the registry since they are not local server accounts or ad accounts
Strange that they are requesting that I have never come across that type of request
That being said there are a few undocumented stored procedures that are registry specific
If memory serves me right I think public has access to them  but I would have to check
Jose TorresCertified Database AdministratorCommented:
To add
In SQL 2012 public still has access to xp_regread, and xp_instance_regread

If the vendor app is calling any of the following stored procedures I would personally ask them why.
1. They are undocumented stored procedures
2. Microsoft can change or delete at anytime
3. In the wrong hands they can cause havoc on your server as you can tell by the names
xp_instance_regaddmultistring
xp_instance_regdeletekey
xp_instance_regdeletevalue
xp_instance_regenumkeys
xp_instance_regenumvalues
xp_instance_regread
xp_instance_regremovemultistring
xp_instance_regwrite
xp_regdeletekey
xp_regaddmultistring
xp_regdeletevalue
xp_regenumkeys
xp_regenumvalues
xp_regread
xp_regremovemultistring
xp_regwrite
James DanahayDirectorAuthor Commented:
Waiting to hear back from the vendor
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.