Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

General discussion


Sharing an Excel Spreadsheet that access SQL Server

By elbmag ·
I have set up an Excel Spreadsheet that accesses a SQL Server database for several users. The queries are working fine and everyone is happy with the results. One problem I haven't been able to resolve is when any user on any machine (including myself) opens the spreadsheet they are presented a SQL Server Login window. The window shows the server name, the username, and the password (the option for use Trusted connection is not checked). Why are the users getting this window? What can I do to prevent them from getting this window?

Thank you for any help.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Use a connection string

by nicruzer In reply to Sharing an Excel Spreadsh ...

WARNING: This suggestion lacks strong security measures. Use at your own risk.

I recently completed a small project in Microsoft Access that overcame this issue by supplying the connection parameters through code. The downside, of course, is that the password and username are hard coded. You can lock your VBA project to hinder cracking, but it is not, by far, unbreakable.
In any event, when you first open your workbook (using the AutoOpen macro, possibly), setup the connection using a connection string with the following template: "Provider=SQLOLEDB.1;Data Source=<ServerName>;Initial Catalog=<Database Name>;user id=<User ID>;password=<Password>" [Change the parameters to suit your needs.]
Plenty of online resources exist to get you going in the right direction with this sort of thing using ADO technology in Excel. May all go well with you!

Collapse -

I would like to send you an Excel Workbook

by hutchkl In reply to Sharing an Excel Spreadsh ...

I have a problem that is similar. I have an Excel workbook that I want to connect to an SQL 2000 database and gather data to report in the Excel workbook. I have developed quieries and can extract the data but converting it presetns the problem. In the database the example is showing data as a number and I want to convert this number to be read as text in the workbook. Then calculate the text and report this again as a number. If you could get me a solution on this I am working on your issue. I think to deal with your issue you could use two things. First, a VB applcation that logs you into the SQL Server and then also the Excel program. You could designate then the password as "something" in the VB line of code or declared variable. The other possiblity if map the users to the program ylu are running and bypass the login. I have a third but will get you the exact details.

Collapse -

Changing types

by alan williams In reply to I would like to send you ...

To display the numbers as text either
1. Format the column\cell as text
2. Use the old trick of appending a single quote to the front of the number befor placing it in a cell.

to change back to a number, there are many ways.
ie use the Val function, or cint ... (although this will throw an error if the value is not numeric, the Val function just returns a 0)
or format the column\cell back as a number.

Collapse -

Need more information

by cvestal In reply to Sharing an Excel Spreadsh ...

To best answer your question more information is needed:

1) Are users on a trusted domain that uses Windows Authentication or Active Directory?

If so, and if you manage the SQL Server, then you could configure SQL Server to use Windows authentication.

2) What type of connection are you using, ODBC or ADO?

If it is ODBC data source, then you should not have to use a connection string and hard code the passwords - the data source would have this information embedded inside of it and would be more secure...


Collapse -

Additional information

by elbmag In reply to Need more information

This is an ODBC data source and the users and the server are all on the same domain using Active Directory.

Collapse -

ODBC "Security"

by nicruzer In reply to Additional information

ODBC connection strings are actually stored as plain text in the registry. Anyone with access to the registry will have access to stored ODBC passwords. Look in the HKCU/Software/ODBC key. You'll find the info you need in there.

I still recommend encrypting your passwords in an executable or something similar.

Collapse -

give them access

by jscarrozzo In reply to Sharing an Excel Spreadsh ...

give them read access in the database it is looking at. i just recently ran into this, so i figured I might as well give them access to read modifying.

Related Discussions

Related Forums