SQL Excel

I apologize in advance if this is a dumb question. I'm not a programmer; just a user trying to take advantage of this slick tool.

Here's a background. Excel is on my corporate laptop. SQL is on a network monitoring server at a remote location. I have both a VNC and a mapped drive to the SQL server. Running SQL Server 2005. When I log into SQL I use Windows Authentication.

I tried to build a connection profile. For Provider is selected Provider for SQL Server. In the connection tab I used the IP address for the server name, Windows NT Integrated Security and the correct database name. I keep getting an error: Test connection failed because of an error in initializing provider. Login failed for user ". The user is not associated with the trusted SQL Server connection.

This is the way I log onto SQL when I'm on the server. What am I doing wrong???

Thanks.

Hi

Seems like you can connect that way only when you logon to the server itself ?

Please take a look at this page I found. I hope it will help you get this going (its for SQL 2005 express but it references the same error you mentioned). Please let me know how it goes. Thanks, Al
http://www.linglom.com/2007/08/31/enable-remote-connection-to-sql-server-2005-express/

Thanks for the suggestion. I'm getting closer but I'm not there yet. I did verify that remote connections were already enabled. I also enabled SQL authentication. The system would not allow me to use the sa user, so I created a new user called Excel. I successfully tested the ability to log on locally with Excel. Then I went back into SQL Excel and created the connection. I was able to successfully test the new connection. But when I went to actually connect I received an error from SQL Excel stating "Login failed for user 'Excel' (CommandConnect_Click)." So while I was able to do a successful connection test I can't make an actual connect.

I read another article that says remote connection should be via a specified port. If not, SQL Service Browser has to be enabled, which presents some security concerns. Can I assume you use a specific port?

Any thoughts?

Hi

Good you are making progress on this.

To see what the exact error is, please can you take a look in the error log in C:\Documents and Settings\[USERNAME]\Application Data\sqlexcel (this is the default location on XP...)
It would be good to see the exact error.

It would be a good thing to check too that you have the same issue from other sql clients on your machine. You could try with Query analyser or with MS query or a different client.


For enabling the port, it sounds like a more secure way than the sql browser. You can use a non standard port. You can also restrict access to a specific IP. These two techniques will give you some extra security.

If you have a security expert in your company it would be the best thing to run everything by them.... I did find a few articles that are worth a read if you havent seen them already...

General notes on IP restricting: http://blogs.msdn.com/sql_protocols/archive/2006/04/10/572605.aspx

Non standard port notes: http://www.databasejournal.com/features/mssql/article.php/3689846/Using-Non-Standard-Port-for-SQL-Server.htm


Again, best thing here would be to check everything with a security expert or at least a networking specialist.. If not, I would read up as much as possible on MSDN...

Please let me know how it goes. This information will probably be very useful to other people here I would think..

Thanks again and have a nice day.
Al

I'm afraid I've had no further luck. I loaded SQL Management Studio on my laptop and was able to make a remote connection using the default port and the same Excel user name that I'm trying with SQL Excel. I'm still getting the same "Login failed for user Excel" message. I think I'll have to put this aside for now and go another route. I need to be able to pull some query results into Excel for analysis, and my boss doesn't give me unlimited time to try neat tools

Thanks for the various suggestions.

Hi

Sorry it did not work out on that. I think it should be just a matter of playing around with a few settings.. But might be a bit messy.

If you do happen to get it working fine, please let us know if you do get it working though as it would be very useful I think for some people here.

Thanks again and take care
Al