Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBscript Sybase Query DSN credentials

Posted on 2011-02-10
11
Medium Priority
?
1,670 Views
Last Modified: 2012-05-11
Hi,

I need to run a vbscript (from a windows 2003/2008 server) that will use a system  DSN ODBC connection to a sybase database on a Unix  server to do a query. (select statement)
I cant have the password in the script (cleartext) so I want to configure a DSN with the username and password in there .

It this possible?
Can I call a DSN odbc connection from VBscript and use something like trusted for the credentials?
If so, what do I use to open the connection to the DB?
The user account and password will be  local sybase user/pass.

0
Comment
Question by:neoptoent
[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
  • 6
  • 5
11 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 34867069
Hi, this should show you some examples on how to use a System DSN:
http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/enterprise/databases/

This line should cover the main connection part:
objConnection.Open "DSN=Inventory;"


Regards,

Rob.
0
 

Author Comment

by:neoptoent
ID: 34867629
Rob,

What do I do about password.
I dont think you can store a password in a dsn.
And i need to connect to Sybase and Oracle databases using sql authentication.

I can have it in the connection string because we cant have a password in clear text

Any ideas?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34867724
I don't actually think it's possible.  With VBScript, the details will always be in clear text somewhere along the line, whether you encrypted them or not.  There is no way to predefine SQL authentication credentials in a DSN.

Perhaps your best bet is to put the connection details in the script without using a DSN (so use a DSN-less connection string), and then compile the VBS into an EXE, using something like the VBSToEXE tool here:
http://www.f2ko.de

Regards,

Rob.
0
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 

Author Comment

by:neoptoent
ID: 34867738
Not a bad idea,
If  it is converted to an exe, is anything still in clear? meaning can someone open the exe and see the password?


I wish there would be  a way for me to encrypt the password in a reg key or file and then use the vbscript to call that key/file decrypt the password.


If I use a dsn then the password stays encrypted over the wire...
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 34867980
If it's an EXE, it's FAR more difficult to decompile, but I guess not "impossible".  Another idea, maybe more secure, is to write the script in Visual Studio Express, as a .NET application, which is compiled into an EXE.

There *is* a way to encrypt something to a file, but it requires an encryption "key", and the key would need to put into the VBScript, meaning someone only has to copy your code, and use the encryption key to decrypt the file themselves.

People have tried to do what you're doing everywhere:
http://stackoverflow.com/questions/1244350/sybase-iq-and-encrypted-passwords

The most common solution is just lock down a specific account to allow only what you need, and put that in your connection string.

Regards,

Rob.
0
 

Author Comment

by:neoptoent
ID: 34874150
ahhh

i dont know .net... can i use vbscript in there and just compile it?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34876320
No, unfortunately, that doesn't work, you might just be best trying out the VBSToEXE approach.

Regards,

Rob.
0
 

Author Comment

by:neoptoent
ID: 34880210
if i wrote it VB and compiled it, can u encrypt the code so it cant be viewed?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34881747
If you conver the VBS to an EXE, it is written in "machine code", so cannot be interpreted.  I've tried running a few "sniffers" over the EXE compiled by the VBS2EXE, and couldn't find any clear text code, so I think it's pretty safe.

Regards,

Rob.
0
 

Author Comment

by:neoptoent
ID: 34885051
Rob,

do you think i could use blowfish, encrypt the password in the registry and use it?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34885143
I assume you're talking about something like this:
http://www.example-code.com/vbscript/vbscript-blowfish.asp

But, as far as I can see, if people can still read your plain text vbscript, then they could make a new script that includes these lines (in particular):

keyBytes = blowfish.GenerateSecretKey("secret_password")
blowfish.SecretKey = keyBytes

Then read the cypherText (encrypted string) from the registry where you store it, and simply decrypt it using this:

clearText = blowfish.DecryptStringENC(cipherText)

VBScript really isn't the secure way to do this, because it's always plain text.

There might be one way of making the SecretKey unknown though, or if we go back a few steps, the database username and password for the DSN, unknown.  That would be to pass those values from AD as a login script perhaps.  The parametes passed from AD are unkown to the user, and they can't copy the code to try to run it from somewhere else, because it needs to run from that particular GPO.

To do that, use a DSN-less connection string, and code like this:

strUsername = WScript.Arguments.Item(0)
strPassword = WScript.Arguments.Item(1)
strConnString = "Data Source=\\myserver\myvolume\mypat\mydd.add;User ID=" & strUsername & ";Password=" & strPassword & ";ServerType=REMOTE;"

Connection strings are here: http://www.connectionstrings.com/sybase-advantage

Then, in a GPO, set up a login script to point to the script, and in the parameters section type
username password

Regards,

Rob.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

661 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