"Server type" is disabled in SQL Server Management Studio

Hi,

I'm trying to connect to a remote server using SQL Server management studio express.

But the "server type" field is disabled. I'm not able to select the server type. It is set to "Database Engine". Because of this (I Guess) the connection to the remote SQL server 2005 is failing giving the following error:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)"

Please let me know how to solve this issue.

Thanks & Regards,
Bijesh

Hi there,

Is the remote server running either of SQL Server 2005 Express Edition or SQL Server 2005 Developer Edition?

The error mesasge you posted seems to indicate that the remote server won't accept a connection. I don't think this has anything to do with the value in the "Server Type" field.

If you're trying to connect to a remote instance of SQL Server 2005 Express or Developer Edition then remote connections are disabled by default & this would explain why you are unable to connect. If you perform the steps outlined in the below link on your remote server, you should be able to connect to the remote server:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Also check that the name you are providing for the remote server is correct. If the remote server is running a default instance then the name of the SQL Server is the name of the computer. If the remote server is running a named instance then the name of the SQL Server is <Computer Name>\<Instance Name>

Hope that helps a bit, but sorry if it doesn't
|||SQL Server Express can only connect to the relational engine - that's why server type is grayed out.|||

This means the client couldn't make a connection to the server. Most often, the cause is because the server is not configured to accept connections from other machines. Second most often, there is a firewall getting in the way.

Make sure the remote server is configured to accept named pipes connections. You can check this using the Surface Area Configuration tool.

If named pipes don't work for you, switch to using TCP/IP connections, make sure the server is configured to accept TCP/IP connections, and make sure the IP port used by SQL Server is open in your firewall (by default, this is port 1433).

Hope this helps,
Steve

"No Such Interface Supported" Exception 0x80004002 - HELP!

AAAAARRRRRRRRRRRGGGGGGGGG!

I finally figure out why I can't connect to the SQLEXPRESS on this computer (technically, I was a remote connection), get everything installed (Advanced edition), and then...

It keeps telling me that "No Such Interface is Supported" and "Unable to cast COM object of type "System._ComObject" to Interface type..."

I grabbed it in a screen capture and will try to get it to a server tonight when I get home.

Any ideas on this problem?

Jason

http://members.cox.net/pamela444/errorwindow.jpg

Well, it won't let me insert a link, but the photo's at that URL.

|||

This error comes from VS. Have you determined if SQL Express is functioning correctly by itself?

On the computer you are getting this error, try connecting to SQL Express using SQLCmd, open a command prompt and type:

SQLCmd -S <machinename>\sqlexpress -Q "select @.@.version"

If this returns the version of your server, then you know that SQL Express is functioning correctly and that you have it configured to accept the connection if you're trying to access it remotely. Info on configuring remote connections is at:

914277 How to configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

"Named Pipes Provider: Could not open a connection to SQL Server [53]", but only on th

My connection string (to a remote instance of SQL Server Exress 2005) is exactly the same when the web files are located on my local machine or my remote machine, and while it works fine when I use the local web files, I get the following error when I use the remote files:

"Named Pipes Provider: Could not open a connection to SQL Server [53]"

As you can see, since it works from the local version, I've setup SQL to accept remote connections via TCP/IP and Named Pipes. I'm using IIS7 on the local, and IIS6 on the remote, in case that makes a difference. I have other sites setup the same way, which work fine.

Any thoughts?

Open the configuration manager and set SQL Server to allow remote connections.
This is off by default on SQL Server Express Edition.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Thank you for responding, although as you can see in my question, I point out that this is clearly set up, as I am connecting successfully from my local machine.

However, I should note that I have resolved the problem by replicating the database.
|||

Oops, I didn't read that little sentence, sorry :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

"Connect Object Explorer" issue

HI All,

When I connect to a remote database server my IP gets stored in the Object Explorer connection wizard. How do u ensure that once the server is disconnected you have to reenter the server IP again.

It also shows the username as soon as i enter the server name. I don't want this to happen. Please help.

A SQL Server MVP had the same question not too long ago. We found him an answer and he posted it on this blog:
http://devcow.com:80/blogs/adnrg/archive/2006/09/08/Clear_SQL_Server_2005_Management_Studio_recent_servers_drop_down_list.aspx

Basically, there is no way to clear it out through the UI. You have to delete the following file:
C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

<Newbie> Problem with Server-Name in Connect to Remote Server

Dear group,

kindly please answer me my newbie question :-)

I want to connect to a remote SQL-Server using sp_addlinkedservers and
"openquery". I tried and tried and I couldn't get it to work. Then i
found out, that it works with a different server which doesn't have
the '-' character within its servername.

So

sp_addlinkedserver 'ULI-SERVER'
select * from openquery(uli-server, 'select * from
northwind.dbo.sometable')

yields a syntax error ("Incorrect syntax near '-'")

sp_addlinkedserver 'someotherserver'
select * from openquery(someotherserver, 'select * from
northwind.dbo.sometable')

is working

Am I doing something wrong ? Is there a workaround ? Do I have to
rename servers which would be tedious for us.

Thanks for any hints

Greetings from Vienna

UliUli (uli2003wien@.lycos.at) writes:
> kindly please answer me my newbie question :-)
> I want to connect to a remote SQL-Server using sp_addlinkedservers and
> "openquery". I tried and tried and I couldn't get it to work. Then i
> found out, that it works with a different server which doesn't have
> the '-' character within its servername.
> So
> sp_addlinkedserver 'ULI-SERVER'
> select * from openquery(uli-server, 'select * from
> northwind.dbo.sometable')
> yields a syntax error ("Incorrect syntax near '-'")
> sp_addlinkedserver 'someotherserver'
> select * from openquery(someotherserver, 'select * from
> northwind.dbo.sometable')
> is working
>
> Am I doing something wrong ? Is there a workaround ? Do I have to
> rename servers which would be tedious for us.

- is not a legal character in identifiers. Whence the syntax error.

Fortunately, the rememdy is simple:

select * from openquery([uli-server],
'select * from northwind.dbo.sometable')

Whenever you have a name in SQL Server which does not adhere to the
grammar for identifiers, but the name in brackets. (Or in double quotes,
if you want stick to ANSI standards.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

#URGENT# Remote Connections to Server

Hi, I've been trying all day to make connections to SQL server 2005 express work. I have gone throgh the setup of tcp/ip, firewall exceptions, and anything else possible. My problem is that after i have gone through the microsoft support pages.

I can connect to SQL server in VS2005 but i can connect and make changes to databases with SQL Server Management Studio. In VS2005 i try to connect and i get the error System.Data.SqlClient.SqlException

Message="An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)"

I have to write a program by the end of the week and cannot proceed without SQL Server working proerly. Any replies would be apreciated as i am so fustrated with this problem. I have included the full error stack below if it is required. Contact me for any more details

Regards
Matt

System.Data.SqlClient.SqlException was unhandled
Class=20
ErrorCode=-2146232060
LineNumber=0
Message="An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)"
Number=2
Server=""
Source=".Net SqlClient Data Provider"
State=0
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Tour_Operator.DataSet1TableAdapters.ToursTableAdapter.Fill(ToursDataTable dataTable) in C:\Documents and Settings\Matty\My Documents\Visual Studio 2005\Projects\Computermat-IT\Tour Operator\Print\DataSet1.Designer.vb:line 1332
at Tour_Operator.main.main_Load(Object sender, EventArgs e) in C:\Documents and Settings\Matty\My Documents\Visual Studio 2005\Projects\Computermat-IT\Tour Operator\main.vb:line 18
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Tour_Operator.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Hi check the following things.

1.) check the servername displayed in SSMS (eg.servername\SQLEXPRESS) while connecting and use the same servername in vb code.
2.) By which authentication mode you are connecting (windows or SQL), if its SQL , the check SQLEXPRESS is configured in mixed mode
3.) Check SQL services are up

|||Hi, thanks for the reply. However, i have sorted the problem, dont know what was cuasing becuase i could connect to the database with the SMSE but not vs2005. I have reformatted my server machine and reinstalled everything and it works fine now. The first time i installed sql server i had to do it many times as it was error after error so maybe a currupt file caused my problem with connections.

Still strange with connection becuase i can fully connect to the database in vs2005 but if i run the "OSQL" cmd test it doesn't connect.

But thanks anyway for replying.

Regards
Matt
|||What is the error message you are getting while connect using OSQL?

Try connect with SQLCMD , this shoud work
|||

Under SQL Server Surface Area Configuration Manager, check if Remote Connections are Enabled via Named Pipes and TCP/IP

Also, verify that the Protocols TCP/IP, Named Pipes are enabled from SQL Server Configuration Manager