[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Create a Universal User for SQL2005 express

Posted on 2009-07-12
18
Medium Priority
?
387 Views
Last Modified: 2012-05-07
I have a VB6 app that connects to SQL2005 express through a network. Right now I have to take all of the users and add them to the SQLSERVER2005SQLUser, SQLSERVER2005SQLBrowserUser on the Server. Then add them to the Logins on the DB and give them permissions on the SQLExpress/permissions. Every time I have a new person come on board I have to get their login ID and go through the process again. Corporate now wants me to install this program throughout the company.... thousands of users.

My question is... can I create a universal user and add that user and password to my connection string in the VB app? (similar to 'sa', 'password') Right now SQLExpress is using windows authentication. Would I then need to switch it to SQL Server Authentication?
0
Comment
Question by:md0333
[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
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 6

Accepted Solution

by:
microbolt earned 2000 total points
ID: 24835226
Yep, change it to mixed mode.  This will allow windows logons and SQL authentication.  Then create an logon for your database.  I would not recommend using SA as it is not an very secure practice.  Create another user just for this database.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24835231
create a domain GROUP in windows active directory.
then, create in your sql the login based on that group.
grant that group the permissions.

make all the existing users members of that group in AD.
make all new users members of that group in AD.

remove all existing users from the sql server security that only had that profile.
0
 

Author Comment

by:md0333
ID: 24835311
OK... Changed SQLExpress to mixed mode... created a new User on the Server. I then went to my database/Security/Users and tried to add that user but it would not. How do I create a logon for the DB? I must be doing it incorrectly...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Expert Comment

by:microbolt
ID: 24835345
Need to make the user first under "Security -> Logins"
0
 
LVL 6

Expert Comment

by:microbolt
ID: 24835364
Here is an screenshot of where it is in SQL Management Studio
Screenshot.jpg
0
 

Author Comment

by:md0333
ID: 24835430
OK... Got the user created. Added that user to my Database. User I created is SDS... DB name is SDS also. Changed my connection string to look like this:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Dim sDB As String
   
    sDB = "SDS"
    gsServerName = "ACJTS01\SQLEXPRESS"
   
    ' Specify the OLE DB provider.
    cn.Provider = "sqloledb"
   
    ' Set SQLOLEDB connection properties.
    cn.Properties("User ID").Value = "SDS"
    cn.Properties("Password").Value = "password"
    cn.Properties("Initial Catalog").Value = sDB    
   
    ' Windows NT authentication.
    cn.Properties("Integrated Security").Value = "SSPI"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Now when I try to login I get this error.

Run-time error '-2147467259 (80004005)':
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

What am I missing?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 24835440
If you establish a common login for all users, then you need to invent your own mechanism for distinguishing one from the other (think audit trail) and everyone will have the same permissions.  Not always the best arrangement.

Often it's better to use Windows authentication as Angel suggests, and manage the database security at the group level.  In it's simplest form, you can create a single Windows group  and manage database permissions to that group.  Then membership in that group entitles one to the associated permissions.

Slightly more work that a singular login, but well worth it when corporate comes along with their next request.
0
 
LVL 6

Expert Comment

by:microbolt
ID: 24835441
try commenting out this line:

cn.Properties("Integrated Security").Value = "SSPI"
0
 
LVL 42

Expert Comment

by:dqmq
ID: 24835456
The following is wrong when using SQL Authentication.    

cn.Properties("Integrated Security").Value = "SSPI"

change to:

cn.Properties("Integrated Security").Value = False
0
 

Author Comment

by:md0333
ID: 24835475
microbolt - I tried it with and without that line of code.... same issue.

angel and dqmq - my problem is that "corporate" is different domains all over the US. I will not have access to these domains or servers other than installation time. They don't all necessarily have IT guys so I'm trying to simplify this as much as possible. If I'm understanding what you are saying correctly, they would still have to have someone add any new employees to this group you are suggesting I create. I'm trying to think of this implementation as.... load software, DB, create universal user account and be done. I can update the software with a new .exe file but won't have to worry about new employees as long as the software is on their computer. The data is not sensitive...
0
 

Author Comment

by:md0333
ID: 24835487
ok... tried changing the code to

cn.Properties("Integrated Security").Value = False

got a different error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

???
0
 
LVL 6

Expert Comment

by:microbolt
ID: 24835497
Try changing:

    cn.Properties("User ID").Value = "SDS"
    cn.Properties("Password").Value = "password"
    cn.Properties("Initial Catalog").Value = sDB
to

    cn.Properties("uid").Value = "SDS"
    cn.Properties("pwd").Value = "password"
    cn.Properties("Database").Value = sDB
0
 

Author Comment

by:md0333
ID: 24835528
micobolt - no joy

VB does not recognize those... "Cannot be found in the collection corresponding to the requested name or ordinal"
0
 
LVL 6

Expert Comment

by:microbolt
ID: 24835558
Never actually saw you put the data source in.  Is it somewhere else in your code:

cn.Properties("Data Source").Value =  gsServerName;

And your right the "user id" and "password" are the correct fields.  You should be able to just comment out the "Integrated Security" and it work.
0
 

Author Comment

by:md0333
ID: 24835592
looks like we are getting closer... I added the Data Source line of code (had it commented out... idiot) and now I'm getting another error when I try to load the program but it has to do with the PRODUCT table in my db.

The SELECT permission was denied on the object 'PRODUCT', database 'SDS', schema 'dbo'.

0
 
LVL 6

Expert Comment

by:microbolt
ID: 24835603
Did you give your user proper permission in SQL management studio?  If you want full access it needs db_owner privilege.
0
 

Author Comment

by:md0333
ID: 24835607
I got it... I had to go into the DB and give the SDS user db_datareader and db_datawriter Schema and Role...

Now, program seems to work fine... any other things I should add to this user?
0
 

Author Comment

by:md0333
ID: 24835608
OK... gave it db_owner.  Thanks microbolt!!! Awarding points now!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.
Suggested Courses

656 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