February 21, 2008 at 3:02 am
Hi everyone, I'm new here!
I've begun work on a project for a new client, and we have a dedicated server set up with Fasthosts. To save cost, I've decided to install SQL Server 2005 on the same machine that the site will be running on.
Well, I've installed SQL Server 2005, and can connect to it from within the server itself, but I cannot connect from outside. I've looked at firewalls, security policies etc.. but still I'm getting the same error:-
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)
I have created another user within SS2005, but above I'm just showing 'sa' for example (although the same error occurs when using the default 'sa' login).
PLEASE can someone help me, or suggest things to try. Fasthosts themselves aren't helping much, because essentially they can't support this type of setup (they have their own dedicated SQL Servers, which we opted not to use).
Many thanks in advance
Andy
February 21, 2008 at 6:01 am
I'd suggest a training course - if you're really hosting a sql server this way you shouldn't be asking this question. I know this sounds harsh but data security is important and I'm not sure I'd feel happy as a business if I employed you and saw this question.
You probably need the surface configuration, generally remote access is disabled by default.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 21, 2008 at 6:27 am
Harsh yes. I can see how eyebrows might be raised at the question, and I might come across as a bit of a newbie. I am new to server configuration etc..., but not necessarily to SQL in general.
Be that as it may, this is the situation I am in.
I have already been through the Surface area config, and everything seems fine to me. I cannot see any reason why I cannot connect to the server through the IDE. I can remote in fine, the environment works fine locally. Everything for remote connectivity is set to enabled/TCP/IP.
I'm really stuck, and would love some positive help here.
Cheers
February 21, 2008 at 12:01 pm
colin Leversuch-Roberts (2/21/2008)
I'd suggest a training course - if you're really hosting a sql server this way you shouldn't be asking this question. I know this sounds harsh but data security is important and I'm not sure I'd feel happy as a business if I employed you and saw this question.You probably need the surface configuration, generally remote access is disabled by default.
I don't think this sort of reply belongs to this forum. We all have varying degrees of expertise working with SQL Server, from newbee to MVP, and so we are at different stages of our professional development. This kind of a response discourages free and open discourse, which is the essence of this site.
So PLEASE let's stick to the technical stuff and leave the disparaging aside.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 21, 2008 at 12:08 pm
Yup - I agree, I was hoping for some help here after all, not some unnecessary thoughtless comment.
February 21, 2008 at 12:11 pm
andym0908 (2/21/2008)
Harsh yes. I can see how eyebrows might be raised at the question, and I might come across as a bit of a newbie. I am new to server configuration etc..., but not necessarily to SQL in general.Be that as it may, this is the situation I am in.
I have already been through the Surface area config, and everything seems fine to me. I cannot see any reason why I cannot connect to the server through the IDE. I can remote in fine, the environment works fine locally. Everything for remote connectivity is set to enabled/TCP/IP.
I'm really stuck, and would love some positive help here.
Cheers
Kind of a shot in the dark, but is your SQL Server Browser service running?
Also, in similar situations, what has worked for me is creating a System DSN using Named Pipes to the remote instance. Once that's created, even if I delete it afterwards, I can connect fine, and the problem is solved. I don't understand the mechanism behind this, I just know it works.
Fasthosts is something I am not familiar with.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 22, 2008 at 1:23 am
Ok, so how do I create local DSN using 'named pipes' to the server. I tried, going to Admin Tools - Data Sources (ODBC) -> Add DSN - but using the same credentials to create a Local DSN to the server doesn't work/connect. What should I be doing?
Oh, and Fasthosts is simply the name of the hosting company.
February 22, 2008 at 7:58 am
Unfortunately, I cannot look it up from where I am right now, so I can only tell you from memory.
I think there is an Advanced (or Configure) button when you create the system DSN. Once you press that, it will give you a dialog where you can choose the network protocol.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 22, 2008 at 8:01 am
The other way is to launch SQL Server Client Configuration (from SQL Server Configuration Manager) and create an alias to your remote server, again using named pipes.
Again, I'm talking from memory, I don't have access to these tools at the moment.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 22, 2008 at 8:11 am
Hi Andy, what version of SQL Server are you running? Express has remote connections disabled by default. This can be enabled in the surface area configuration tool. Can you telnet to the instance on TCP 1433? i.e. Start -> Run -> cmd -> telnet MYSERVERNAME 1433
Tommy
Follow @sqlscribeFebruary 22, 2008 at 8:18 am
Hi Tommy
I'm using SQL Server 2005 Developer Edition... remote connections are enabled and have been for some time.
February 22, 2008 at 8:19 am
Hi Marios
Ok, I'll give that a shot, and see what happens!
February 22, 2008 at 9:59 am
Andy, on the server in question - can you post the results of the following query -
select * from sys.endpoints
Tommy
Follow @sqlscribeFebruary 22, 2008 at 10:04 am
Hi Tommy
Ok, I did that and the results are in a table:
Dedicated Admin Connection112TCP2TSQL0STARTED1
TSQL Local Machine214SHARED_MEMORY2TSQL0STARTED0
TSQL Named Pipes313NAMED_PIPES2TSQL0STARTED0
TSQL Default TCP412TCP2TSQL0STARTED0
TSQL Default VIA515VIA2TSQL0STARTED0
February 22, 2008 at 10:17 am
Andy - what's the rest of the error message? This link actually might help you decipher what the server is trying to tell you....The "state" listed is actually giving you specific info as to the failure in question.
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply