Software

Ask something
Discussion Locked

Sharing an Excel Spreadsheet that access SQL Server

0 helpful
7 replies

by elbmag 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

7 replies

by nicruzer

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=;Initial Catalog=;user id=;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!

by hutchkl

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.

by alan williams

To display the numbers as text either
1. Format the column\cell as text
or
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.

by cvestal

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...

CV

by elbmag

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

by nicruzer

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.

by jscarrozzo

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 it...no modifying.

This discussion is closed to new replies.