Results 1 to 5 of 5

Thread: Excel 2010 SQL Server Connection

  1. #1

    Excel 2010 SQL Server Connection



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    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.

    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?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    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.

    Click image for larger version. 

Name:	1.png 
Views:	575 
Size:	14.1 KB 
ID:	1814

    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 image for larger version. 

Name:	2.png 
Views:	204 
Size:	19.3 KB 
ID:	1815

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

    Click image for larger version. 

Name:	3.png 
Views:	245 
Size:	34.8 KB 
ID:	1816

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

    Click image for larger version. 

Name:	4.png 
Views:	434 
Size:	44.4 KB 
ID:	1817

    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;
    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?
    Regards,
    Zack Barresse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •