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
Solved

How to set the sa password in a vb6 code for sql connection

Posted on 2016-11-05
9
58 Views
Last Modified: 2016-11-06
We would like our vb6 apps to connect to a MS Sql server without the user entering SA or its password, rather have some sort of code to access it and use it for connection.  Can this be done? How/
0
Comment
Question by:rayluvs
  • 5
  • 4
9 Comments
 
LVL 21

Expert Comment

by:JesterToo
ID: 41875639
A good solution to this problem depends on a number of factors not defined in this question.  I recommend reading this link

  https://www.mssqltips.com/sqlservertip/1831/using-windows-groups-for-sql-server-logins-as-a-best-practice/

for a good start at understanding some of your options and their ramifications.  Some factors are going to be whether your organization uses Active Directory, how many users you have, and how varied their database permissions may be.

The 2 most serious concerns I have regarding user/application access to a database is:
#1 never give SA access to users, and
#2 never embed username and password in an application in an un-encrypted manner.

Let us know if you have more questions regarding these choices and any pertinent details about your environment.
0
 

Author Comment

by:rayluvs
ID: 41875716
It's a Windows Server 2008 using AD.  As for "never give SA access to users", we don't want to do that, we want to embed a connection with SA privilege to the code out of the end-users eyes.

Read the link, but how do we do this?  How do we connect to the SQL server with SA privilege?
0
 
LVL 21

Expert Comment

by:JesterToo
ID: 41875778
Steps 3 thru 11 can be done either thru Sql Server Management Studio or thru scripts... SSMS instructions are shown and scripting method can be easily found thru online searches.

1. Create a new AD Group.
2. Add the users to the AD Group.
3. Create a new "login" for the AD Group in SQL Server.
4. Choose "Windows Authentication" for this new login.
5. Click on "Search".
6. Ensure "Object Type" is set to "User or Built-in Security Principal).
7. Click on "Locations".
8. Place a "Check" in box beside "Groups".
9. Type in the name of the AD Group in the dialog box.
10. Click "Check Names".
    If the name you just entered is underlined, then you're good up to this point, otherwise go back and check everything.
11. Now, choose what roles to assign to this login.  I highly recommend you do not assign "Server roles" but only use "Database Roles"... unless you want all the group members to be DBA's.

The connection string in the VB6 app needs to specify "trusted" in order to tie this user/group to the database.  Here is a fragment of that connection string...

      Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

The first portion of the string will depend on what database provider is being used.

Hope this helps and good luck!
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:rayluvs
ID: 41875791
Thanx! Just got s break to connect to the sever and try these steps.
0
 

Author Comment

by:rayluvs
ID: 41875800
We connected and but can't create or manipulate the AD.  Nevertheless, reading your entry, we concluded that it sums up to the user having a "Windows Authentication" user.  So we proceed to create a  "Windows Authentication" user for one of the users and it connected; it worked.

But this didn't answer our question.  We want to know if there is a way our vb6 apps to connect to a MS SQL server without the user entering SA or its password in its code? (that is, include a code in the apps that when a regular user uses the apps to connect to a SQL database, it would connects with SA privilege instead the user).  Please note, that we know we can hard-code sa/password to the routine of the apps to connect and run it's task, but we don't want to.

Can this be done?
0
 
LVL 21

Expert Comment

by:JesterToo
ID: 41875835
You still need to assign an appropriate role to the user.  If you insist on giving everyone SA equivalency, then that role is "sysadmin"... although doing that is extremely risky even if the application(s) are the only things transacting the db.  Unless the app's logic is "bullet proof" the database(s) could be catastrophically compromised.

The last thing you would need to do is change the connection string as I showed in earlier comment to use "trusted connection"  in the source code of all the apps and re-build the executables.  Note that a connection string using "trusted connection" DOES NOT SPECIFY USER OR PASSWORD.  I'd also recommend that you change the existing SA password so that the users can no longer login manually.

It's nearly midnight here and I'm gonna get some shuteye!
0
 

Author Comment

by:rayluvs
ID: 41875842
We don't want to change the actual users privileges.  What we want is to place a code within the vb6 to connect with SA privileges.  Based on what we have read so far, this is not possible unless we hard-code it the sa/password in the vb6 apps, correct?
0
 
LVL 21

Accepted Solution

by:
JesterToo earned 500 total points
ID: 41876124
It seems to me that what you need is for the application to have its own login separate from any real user and that login then needs to have SA-equivalency.  There is a "semi-safe" way to accomplish this and it goes back to my point #2 in my first comment... encryption of the connection string.

This is how I would achieve that...

1. Create a new login with "sysadmin" server role.  This is so you can avoid using SA for anything other than legitimate DBA usage.
2. Using whatever method you have available (probably a custom program) encrypt the new connection string containing the new login and password.
3. Store this encrypted connection string in a file where the VB6 application(s) can read it.
4. Modify the VB6 apps to retrieve this encrypted connection string, decrypt it, and use it to connect to the server.

I've used this technique in C# apps to provide secure and transparent connection strings for apps that needed different connections depending on the environment they were running in (dev, qa1, qa2, prod).

But, in my 48 years of experience I've never encountered a situation in which any user-level app required SA privileges to perform its function.
0
 

Author Comment

by:rayluvs
ID: 41876130
Forgot to enter the comment: Thank you very much that is exactly what we need 'the application to have its own login separate from any real user and that login then needs to have SA-equivalency'; we'll get to it now!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

792 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