Excel 2010 SQL Server Connection

Hi All,

I have created a report using Microsoft query in excel.
This report will be use by a few user and it pull out data from the database store in our sevrer.
Noticed when the report is refresh, excel will prompt for the SQL server login.

Is there anyway that we can save the login detail as we will not want to provide the sevrer login to the users.


Thanks.

You could hard code it into the connection string, but it would be available in plain text for anyone to see if they knew where to look. To be honest, the correct way to do this is to set up a security group in SQL server with read only access, and the assign the users to it in the SQL Server security section. That way you don't need to pass out credentials or store them in the file, and they can log in with their own user id's.

Ken Puls said:

You could hard code it into the connection string, but it would be available in plain text for anyone to see if they knew where to look. To be honest, the correct way to do this is to set up a security group in SQL server with read only access, and the assign the users to it in the SQL Server security section. That way you don't need to pass out credentials or store them in the file, and they can log in with their own user id's.

Click to expand...

Hi ken,

Had take your advise to set up the read only access for the user.

Thanks.

Now i have another question which is in my excel there is a few query (Connection), which mean the user are prompt for login serveral time.
Is there a way that we can reduce to one login for all the querieS?

Good question, and honestly I don't know. I haven't used Query, but I've reached out to a buddy to ask.

Curious though, when you set up authentication in SQL, did you set it up based on Windows Authentication, or SQL Server Authentication? Ours is set up under the latter, and I'm never prompted to enter my security info at all. (It just knows based on my Active Directory account.)

Hi yewee,

The login credentials are stored in the connection string for each individual connection. Since MS Query won't allow shared connections (like Power Pivot does) you'd have to edit each one separately. But you can put the login credentials into the connection string itself. It also does not encrypt passwords like other connections types do for you. For this reason I would stay away from MS Query if you can. Personally I've used it before, but generally when it's an Access connection and I want to return a crosstab query, which can't be returned 'normally' through the UI (can with VBA though, which is a good workaround IMHO, because you can reference the Jet engine and do what you want with it). I think personally I'd rather use a plain vanilla SQL connection over MS Query.

Like Ken asked, I'm assuming you have your SQL server authentication not on Windows Authentication? This is of course the easiest method (as I'm sure you've read in other SQL-related materials), but a lot of times people choose mixed mode. I'm also unsure how you actually made your connection. Here are some images from a SQL connection I made using MS Query. You'll see how you can save login credentials, as well as edit it.

When you create your data source connection (e.g. Data ribbon tab | From Other Sources | From Microsoft Query, on the 'Databases' tab on '<New Data Source>') you'll see a dialog box like this, where you set your connection up. I just used the AdventureWorks2012 SQL database to test with. Look at the checkbox at the bottom of the dialog box.



Once you have your connection and you've chosen to save your user ID and password in the definition (read: connection string), you'll get prompted...



Click Yes to that and you go through and set your query up. Once it's setup and you return the data to Excel, if you look at your connections (DATA | Connections), you'll see it listed there, like this...



If you select your query and hit Properties, click on the 'Definition' tab and you'll notice the connection string, like this...



You can edit this any way you want, but you can't encrypt it. (You'll notice my connection string doesn't have a password. I used mixed mode on my SQL Server, so I can login with my user/admin account.) So this is what will allow the connection to pass without being prompted, so long as the security settings are matching on the SQL side, like Ken mentioned.

I think probably the absolute best reference on the web for creating connection strings to almost any kind of data base is www.connectionstrings.com. They have a page dedicated to SQL Server connections...

http://www.connectionstrings.com/sql-server/

Just find the kind of connection you're looking for and it'll have the syntax. So for standard security (non-trusted) it could look like this...

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Click to expand...

If the authentication is correct on the SQL side you shouldn't get prompted for credentials.

In the end it doesn't really matter how you have your query setup, but what your connection string is.

Does that help, or am I too far off the mark?