locked out of sql server management studio

Hi everyone, I hope someone can help me here!

A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:

Cannot connect to server.

Additional information:

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 doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance!

Can you connect to sqlserver at all on the server itself?
Do you know which version of sql2k5 you're running? Do "select @.@.version" to find out - this assumes you can connect to the server somehow.
If you're using sqlexpress version, the ability to connect remotely is off by default. You will need to use SQL Surface Area tool to change the setting.|||

Hey OJ, thanks for trying to get me out of this pickle!

No, I don't "think" I can connect to sql server. Don't I need to add the databases my app is using to sql server in order for my app to work? As I said, this is my first time moving an app to a server.

The result I get when running "select @.@.version" is SqlServer 2000! Dang, I don't even have it running on my own laptop in the right instance of sql server! I'm glad you had me check that out. I'll move everything over to 2005. I think I have the developer version. Whatever version of 2005 I have on my laptop, I should have on the server, which I can't reach at all. I just checked the version of 2005 I have, & it's 2005 v.9.00.1399.06.

I have the Surface Area tool, but I've never used it. What do I do?

Thanks, OJ. I appreciate your time.

Kimmy

|||Kimmy,

Before you get yourself in big trouble with licensing, I suggest you take a closer look at your server and make sure it does not run a developer edition - dev edition means for development only and should not be used for any type of production.

To determine whatever version you have on the server, we will have to fix your connection problem. ;-) Let see if I can walk you through this.

1. Logon to server locally - either via remote desktop (mstsc /console) or at the console itself.
2. Execute "net start" to get a list of started services. Your sqlserver would be one if it's started. If it's not started yet, run "services.msc" to bring up services mgr and start it. You should find you sqlserver under "SQL Server (instance_name)".
3. Now startup sql surface area tool. I.e. Start -> Programs -> sql2k5 -> config tools -> sqlSAC (i.e. "C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe")
4. Select "SAC for services and connections"
5. Pick your sql instance and ensure Remote Connection is set to local & remote.
6. Restart your sqlserver service to take effect.
7. Now you should be able to connect to your sql instance remote from another computer.
8. If your NT login is part of the sql server's local admins, you should be able to run

sqlcmd -S"your_server_name\instance_name" -E -Q"select @.@.version"|||

Hey OJ,

Thanks for your last post...I've been interviewing with recruiters and haven't had time to check what you last said, but I'm on it now.

Mannnnnn, the liscencing issue? What version do I need to use on a production server?

I will respond tomorrow with what I find out about your other comments from your last post.......and THANKS A BUNCH for sticking with me through this!

Kimmy

|||Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,|||

nyaung wrote:

Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,

please post the full error here. there can be many causes for not able to connect.

locked out of sql server management studio

Hi everyone, I hope someone can help me here!

A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:

Cannot connect to server.

Additional information:

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 doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance!

Can you connect to sqlserver at all on the server itself?
Do you know which version of sql2k5 you're running? Do "select @.@.version" to find out - this assumes you can connect to the server somehow.
If you're using sqlexpress version, the ability to connect remotely is off by default. You will need to use SQL Surface Area tool to change the setting.|||

Hey OJ, thanks for trying to get me out of this pickle!

No, I don't "think" I can connect to sql server. Don't I need to add the databases my app is using to sql server in order for my app to work? As I said, this is my first time moving an app to a server.

The result I get when running "select @.@.version" is SqlServer 2000! Dang, I don't even have it running on my own laptop in the right instance of sql server! I'm glad you had me check that out. I'll move everything over to 2005. I think I have the developer version. Whatever version of 2005 I have on my laptop, I should have on the server, which I can't reach at all. I just checked the version of 2005 I have, & it's 2005 v.9.00.1399.06.

I have the Surface Area tool, but I've never used it. What do I do?

Thanks, OJ. I appreciate your time.

Kimmy

|||Kimmy,

Before you get yourself in big trouble with licensing, I suggest you take a closer look at your server and make sure it does not run a developer edition - dev edition means for development only and should not be used for any type of production.

To determine whatever version you have on the server, we will have to fix your connection problem. ;-) Let see if I can walk you through this.

1. Logon to server locally - either via remote desktop (mstsc /console) or at the console itself.
2. Execute "net start" to get a list of started services. Your sqlserver would be one if it's started. If it's not started yet, run "services.msc" to bring up services mgr and start it. You should find you sqlserver under "SQL Server (instance_name)".
3. Now startup sql surface area tool. I.e. Start -> Programs -> sql2k5 -> config tools -> sqlSAC (i.e. "C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe")
4. Select "SAC for services and connections"
5. Pick your sql instance and ensure Remote Connection is set to local & remote.
6. Restart your sqlserver service to take effect.
7. Now you should be able to connect to your sql instance remote from another computer.
8. If your NT login is part of the sql server's local admins, you should be able to run

sqlcmd -S"your_server_name\instance_name" -E -Q"select @.@.version"|||

Hey OJ,

Thanks for your last post...I've been interviewing with recruiters and haven't had time to check what you last said, but I'm on it now.

Mannnnnn, the liscencing issue? What version do I need to use on a production server?

I will respond tomorrow with what I find out about your other comments from your last post.......and THANKS A BUNCH for sticking with me through this!

Kimmy

|||Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,|||

nyaung wrote:

Hi everyone,

I got the same error when I connect to SQL Server 2005 that exists on windows 2003 server from client using windows 2000.
Can you help me with any idea?

Thanks,

please post the full error here. there can be many causes for not able to connect.

sql

locked out of sql server management studio

Hi everyone, I hope someone can help me here!

A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:

Cannot connect to server.

Additional information:

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 doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)

My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going?

Thanks in advance,

Monster

Have you enabled remote connections for the SQL2005 instance with SQL Surface Area Configuration? You can also try to remove User Instance="true" attribute from the connection string if you don't need to generate user instnace.|||

No, because I don't know how to do those things. I'm used to just developing on my laptop, where everything always works great! Could you tell me how to do the first suggestion? And regarding the second suggestion, I think there's something there I need to understand when you transfer an application to a server. Does that mean that somehow the database is included in the app & doesn't need to interact with sql server at all? Obviously, I'm very new to this.

Thank you so much for your suggestions! I'll await your reply!

|||OK, for suggestion 1 you can click start->All Programs->Microsoft SQL Server2005->Configuration Tools-> SQL Server Surface Area Configuration->click?"Surface Area Configuration for Services and Connections"->expand the SQL instance in the tree on the left->Database Engine->Remote Connections->Using both TCP/IP and Named Pipes. For details, you can refer to:
How to configure SQL Server 2005 to allow remote connections

For the User Instance attribute,?if?it?is?true?it?indicates to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller. you can refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

You can use database file included in your application, but that also requires an accessible SQL2005/Express instance to host the database file: we accomplish this by using the AttachDBFileName attribute in the connection string.

I recommend you take a look at this article which may help to understand the way VS2005 using .mdf file:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

|||

Iori_Jay,

THANK YOU FOR YOUR RESPONSE...I haven't been able to check out what you've said, but I will do that tomorrow & get back to you.

Thank you for sticking with me through this!

Monster

|||It's my pleasure What's going on now?|||

Well, I'm finally into mgmt studio and my databases are there. It turned out that in order for sql server to work on the test server it needed more ram, which was why I was locked out (it had nothing to do with the login, apparently).

But now, of course, there are other issues

|||

Whoops...must have pressed something and it posted before I was done...

I have 2 db's there, 1 for a fictitious bookstore, which works absolutely fine. The other is for a fictitious event booking site, which is accessed both traditionally via the db & then the app is rewritten to extract the data access to a web service. Neither of these work. I also have another simple web service that loads Northwind customer info, which doesn't work either.

Here are the connect strings (remember I'm a newbie):

Hey...just had a brainstorm...I'm going to try it & get back...

|||

Ok, brainstorm didn't work.

Here are the relevant sections of web.config:

<

appSettings> //the brainstorm had to do with changing the localhost below to the name of my website, but that didn't help

<

addkey="NorthwindCustomers.CustomerService"value="http://localhost:2285/KSMCADNET/WebServicesRemoting/CustomerService.asmx"/>

<

addkey="EventBookingWebService.EventBookingService"value="http://localhost:2285/KSMCADNET/EventBookingWebService/EventBookingClient/EventBookingService.asmx"/>

</

appSettings>

<

connectionStrings>

<

addname="pubsConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\pubs.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

<

addname="KimsBookstoreConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="KimsBookstoreConnectionString2"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="EventBookingConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Integrated Security=True;Initial Catalog=EventBooking"providerName="System.Data.SqlClient"/>

<

addname="EventBookingConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=EventBooking;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="KimsBookstoreConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="KimsBookstoreConnectionString2Server"connectionString="Data Source=sqlsvr;Initial Catalog=KimsBookstore;Integrated Security=True"providerName="System.Data.SqlClient"/>

<

addname="NorthwindConnectionString"connectionString="Data Source=notebook\SQL2K5DEV;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />

<

addname="NorthwindConnectionStringServer"connectionString="Data Source=sqlsvr;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" />

</

connectionStrings> //sqlsvr is my test server, that my friend built, the notebook source is my dev laptop where everything works fine

The error I get when I try to connect to the data driven EventBooking site is this (so odd, because the bookstore connects fine, although 2 images consistently err, where the others work fine):

Server Error in '/' Application.

---

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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Source Error:

Line 37: da = new SqlDataAdapter();Line 38: da.SelectCommand = cmd;Line 39: da.Fill(ds, "Rooms");Line 40: Line 41: ddlRooms.DataSource = ds;

Source File:e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingClient.aspx.cs Line:39

Stack Trace:

[SqlException (0x80131904): 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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 EventBookingWebService_EventBookingClient_EventBookingClient.Page_Load(Object sender, EventArgs e) in e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingClient.aspx.cs:39 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

The error I get when I try to run the same app written with the web service is this:

Server Error in '/' Application.

---

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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

Source Error:

Line 29: Calendar1.TodaysDate = DateTime.Now;Line 30: Calendar1.TodayDayStyle.BackColor = Color.Tan;Line 31: DataBind();Line 32: Line 33:

Source File:e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingWebServiceClient.aspx.cs Line:31

Stack Trace:

[SqlException (0x80131904): 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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92 System.Web.UI.WebControls.ListControl.PerformSelect() +31 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.Control.DataBindChildren() +216 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +216 System.Web.UI.Control.DataBind() +12 System.Web.UI.Control.DataBindChildren() +216 System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding) +216 System.Web.UI.Control.DataBind() +12 EventBookingWebService_EventBookingClient_EventBookingWebServiceClient.Page_Load(Object sender, EventArgs e) in e:\1ocmcad\EventBookingWebService\EventBookingClient\EventBookingWebServiceClient.aspx.cs:31 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

And the error I get when trying to run the Northwind web service is this:

Server Error in '/' Application.

---

No connection could be made because the target machine actively refused it

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it

Source Error:

Line 45: [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://www.1ocmcad.net/GetCustomers", RequestNamespace="http://www.1ocmcad.net", ResponseNamespace="http://www.1ocmcad.net", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]Line 46: public System.Data.DataSet GetCustomers() {Line 47: object[] results = this.Invoke("GetCustomers", new object[0]);Line 48: return ((System.Data.DataSet)(results[0]));Line 49: }

Source File:c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\fdd4322a\74322047\App_WebReferences.vmho5c06.1.cs Line:47

Stack Trace:

[SocketException (0x274d): No connection could be made because the target machine actively refused it] System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) +1002146 System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) +33 System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) +431[WebException: Unable to connect to the remote server] System.Net.HttpWebRequest.GetRequestStream() +1504525 System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +103 NorthwindCustomers.CustomerService.GetCustomers() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\fdd4322a\74322047\App_WebReferences.vmho5c06.1.cs:47 WebServicesRemoting_ConsumeService.btnGetCustomers_Click(Object sender, EventArgs e) in e:\1ocmcad\WebServicesRemoting\ConsumeService.aspx.cs:22 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

I've enabled remote connections via named pipes & tcp/ip for the dev version of sql server 2005 as you suggested & also enabled sql server browser like the article you referred me to suggested.

I'm going to work on this all weekend, so there won't be any gaps in communication...I hope to hear from you soon!

Kim

|||

Ok, this is for all you guys who are looking for answers. I spent days on this problem (I won't say how many!) and it was all because of my lack of Sql Server knowledge. The initial problem "locked out of sql server mgmt studio" was an issue of my server not having sufficient memory. After that, I kept getting a myriad of login errors, which changed every time I tried another way to connect to the databases.

What the issue was, was that I didn't have permissions & users & logins set up right in management studio. I ended up creating a new user for each database under each databases' Security > Users folder of "BUILTIN\Administrators" and set role memberships so that that account could access the db's.

I hope this helps someone who is as frustrated as I was!

Thanks for all your help, Iori_Jay.

Monster

|||I'm glad to see you've solved the issue, really great job for a person not familiar with SQL|||

Thank you for the compliment! Hopefully one day in the future I will be answering other people's questions rather than asking a ton of them!!!

And on that issue, could you please take a look at my other question?http://forums.asp.net/thread/1471646.aspx

Thanks so much!

Monster

Lock management in SQL Server 2000

Hi,
I am a newbie to SQL Server 2000 and have issues with lock management.
How would I know that a lock is already in place in SQL Server 2000
while I am attempting to update a record, or insert a record in the
same page that already has a lock on it?
I am looking for the equivalent of the Oracle statement:
SELECT * FROM TABLE FOR UPDATE NOWAIT
This statament will immediately return to me a sqlca.sqlcode of -54 (or
resource busy) if it cannot acquire the lock.
I could then loop through the statement for about 30 seconds, waiting
for about 5 seconds depending on the application. If the loop exits at
the end of 30 seconds, I know there is a serious error and can look at
why the record is not available for locking. If I do acquire the lock,
I will break from the loop, complete my DML, rollback/commit.
Does the SQL 2000 LOCK hint behave the same way?
Will the following snippet of code here work in a similar manner?
declare retry_sw int
begin
-- while loop begins
while retry_sw < 10
begin
select @.lock_sw=1 from table where column_1 = '10' a with
lock
if @.@.error<>0 and @.@.rowcount = 0
begin
waitfor delay '000.00.05'
continue
end
if @.@.error = 0 and @.@.rowcount > 0 break
retry_sw = retry_sw + 1
end
-- while loop ends
if retry_sw = 10
begin
print "Unable to acquire lock"
return
end
-- Continue code from this point since we now have a lock.
Thank you for any help on what I am attempting here.
Regards,
z1hou1How about just using SET LOCK_TIMEOUT? Specify the number of seconds you wan
t to wait, then you get
an error that you can trap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<z1hou1@.gmail.com> wrote in message news:1162636791.380539.277830@.b28g2000cwb.googlegroups.c
om...
> Hi,
> I am a newbie to SQL Server 2000 and have issues with lock management.
> How would I know that a lock is already in place in SQL Server 2000
> while I am attempting to update a record, or insert a record in the
> same page that already has a lock on it?
> I am looking for the equivalent of the Oracle statement:
> SELECT * FROM TABLE FOR UPDATE NOWAIT
> This statament will immediately return to me a sqlca.sqlcode of -54 (or
> resource busy) if it cannot acquire the lock.
> I could then loop through the statement for about 30 seconds, waiting
> for about 5 seconds depending on the application. If the loop exits at
> the end of 30 seconds, I know there is a serious error and can look at
> why the record is not available for locking. If I do acquire the lock,
> I will break from the loop, complete my DML, rollback/commit.
> Does the SQL 2000 LOCK hint behave the same way?
> Will the following snippet of code here work in a similar manner?
> declare retry_sw int
> begin
> -- while loop begins
> while retry_sw < 10
> begin
> select @.lock_sw=1 from table where column_1 = '10' a with
> lock
> if @.@.error<>0 and @.@.rowcount = 0
> begin
> waitfor delay '000.00.05'
> continue
> end
> if @.@.error = 0 and @.@.rowcount > 0 break
> retry_sw = retry_sw + 1
> end
> -- while loop ends
> if retry_sw = 10
> begin
> print "Unable to acquire lock"
> return
> end
> -- Continue code from this point since we now have a lock.
>
> Thank you for any help on what I am attempting here.
> Regards,
> z1hou1
>|||Thanks Tibor,
The SET LOCK TIMEOUT does offer a solution. I will try it out and take
it from there.
z1hou1|||While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
timed out) and error 1205 (deadlock...) cannot be trapped directly in
T-SQL. They seem to return control to the client - whatever the client
is and in my particular case, a Java JDBC program using Microsoft's
JDBC drivers.
Is there a way of trapping these errors in T-SQL?
Regards,
z1hou1|||> While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
> timed out) and error 1205 (deadlock...) cannot be trapped directly in
> T-SQL.
This is not what I am seeing. Running below script, I do indeed get the "err
" string in the result:
SET LOCK_TIMEOUT 300
SELECT * FROM test
WHERE c1 between 1 and 5
IF @.@.ERROR <> 0 PRINT 'Err'
(In 2005, you have even more options using TRY/CATCH.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<z1hou1@.gmail.com> wrote in message news:1163475594.291513.50560@.k70g2000cwa.googlegroups.co
m...
> While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
> timed out) and error 1205 (deadlock...) cannot be trapped directly in
> T-SQL. They seem to return control to the client - whatever the client
> is and in my particular case, a Java JDBC program using Microsoft's
> JDBC drivers.
> Is there a way of trapping these errors in T-SQL?
> Regards,
> z1hou1
>

Lock management in SQL Server 2000

Hi,
I am a newbie to SQL Server 2000 and have issues with lock management.
How would I know that a lock is already in place in SQL Server 2000
while I am attempting to update a record, or insert a record in the
same page that already has a lock on it?
I am looking for the equivalent of the Oracle statement:
SELECT * FROM TABLE FOR UPDATE NOWAIT
This statament will immediately return to me a sqlca.sqlcode of -54 (or
resource busy) if it cannot acquire the lock.
I could then loop through the statement for about 30 seconds, waiting
for about 5 seconds depending on the application. If the loop exits at
the end of 30 seconds, I know there is a serious error and can look at
why the record is not available for locking. If I do acquire the lock,
I will break from the loop, complete my DML, rollback/commit.
Does the SQL 2000 LOCK hint behave the same way?
Will the following snippet of code here work in a similar manner?
declare retry_sw int
begin
-- while loop begins
while retry_sw < 10
begin
select @.lock_sw=1 from table where column_1 = '10' a with
lock
if @.@.error<>0 and @.@.rowcount = 0
begin
waitfor delay '000.00.05'
continue
end
if @.@.error = 0 and @.@.rowcount > 0 break
retry_sw = retry_sw + 1
end
-- while loop ends
if retry_sw = 10
begin
print "Unable to acquire lock"
return
end
-- Continue code from this point since we now have a lock.
Thank you for any help on what I am attempting here.
Regards,
z1hou1How about just using SET LOCK_TIMEOUT? Specify the number of seconds you want to wait, then you get
an error that you can trap.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<z1hou1@.gmail.com> wrote in message news:1162636791.380539.277830@.b28g2000cwb.googlegroups.com...
> Hi,
> I am a newbie to SQL Server 2000 and have issues with lock management.
> How would I know that a lock is already in place in SQL Server 2000
> while I am attempting to update a record, or insert a record in the
> same page that already has a lock on it?
> I am looking for the equivalent of the Oracle statement:
> SELECT * FROM TABLE FOR UPDATE NOWAIT
> This statament will immediately return to me a sqlca.sqlcode of -54 (or
> resource busy) if it cannot acquire the lock.
> I could then loop through the statement for about 30 seconds, waiting
> for about 5 seconds depending on the application. If the loop exits at
> the end of 30 seconds, I know there is a serious error and can look at
> why the record is not available for locking. If I do acquire the lock,
> I will break from the loop, complete my DML, rollback/commit.
> Does the SQL 2000 LOCK hint behave the same way?
> Will the following snippet of code here work in a similar manner?
> declare retry_sw int
> begin
> -- while loop begins
> while retry_sw < 10
> begin
> select @.lock_sw=1 from table where column_1 = '10' a with
> lock
> if @.@.error<>0 and @.@.rowcount = 0
> begin
> waitfor delay '000.00.05'
> continue
> end
> if @.@.error = 0 and @.@.rowcount > 0 break
> retry_sw = retry_sw + 1
> end
> -- while loop ends
> if retry_sw = 10
> begin
> print "Unable to acquire lock"
> return
> end
> -- Continue code from this point since we now have a lock.
>
> Thank you for any help on what I am attempting here.
> Regards,
> z1hou1
>|||Thanks Tibor,
The SET LOCK TIMEOUT does offer a solution. I will try it out and take
it from there.
z1hou1|||While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
timed out) and error 1205 (deadlock...) cannot be trapped directly in
T-SQL. They seem to return control to the client - whatever the client
is and in my particular case, a Java JDBC program using Microsoft's
JDBC drivers.
Is there a way of trapping these errors in T-SQL?
Regards,
z1hou1|||> While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
> timed out) and error 1205 (deadlock...) cannot be trapped directly in
> T-SQL.
This is not what I am seeing. Running below script, I do indeed get the "err" string in the result:
SET LOCK_TIMEOUT 300
SELECT * FROM test
WHERE c1 between 1 and 5
IF @.@.ERROR <> 0 PRINT 'Err'
(In 2005, you have even more options using TRY/CATCH.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<z1hou1@.gmail.com> wrote in message news:1163475594.291513.50560@.k70g2000cwa.googlegroups.com...
> While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
> timed out) and error 1205 (deadlock...) cannot be trapped directly in
> T-SQL. They seem to return control to the client - whatever the client
> is and in my particular case, a Java JDBC program using Microsoft's
> JDBC drivers.
> Is there a way of trapping these errors in T-SQL?
> Regards,
> z1hou1
>sql

Lock management in SQL Server 2000

Hi,
I am a newbie to SQL Server 2000 and have issues with lock management.
How would I know that a lock is already in place in SQL Server 2000
while I am attempting to update a record, or insert a record in the
same page that already has a lock on it?
I am looking for the equivalent of the Oracle statement:
SELECT * FROM TABLE FOR UPDATE NOWAIT
This statament will immediately return to me a sqlca.sqlcode of -54 (or
resource busy) if it cannot acquire the lock.
I could then loop through the statement for about 30 seconds, waiting
for about 5 seconds depending on the application. If the loop exits at
the end of 30 seconds, I know there is a serious error and can look at
why the record is not available for locking. If I do acquire the lock,
I will break from the loop, complete my DML, rollback/commit.
Does the SQL 2000 LOCK hint behave the same way?
Will the following snippet of code here work in a similar manner?
declare retry_sw int
begin
-- while loop begins
while retry_sw < 10
begin
select @.lock_sw=1 from table where column_1 = '10' a with
lock
if @.@.error<>0 and @.@.rowcount = 0
begin
waitfor delay '000.00.05'
continue
end
if @.@.error = 0 and @.@.rowcount > 0 break
retry_sw = retry_sw + 1
end
-- while loop ends
if retry_sw = 10
begin
print "Unable to acquire lock"
return
end
-- Continue code from this point since we now have a lock.
Thank you for any help on what I am attempting here.
Regards,
z1hou1
Thanks Tibor,
The SET LOCK TIMEOUT does offer a solution. I will try it out and take
it from there.
z1hou1
|||While the LOCK_TIMEOUT works admirably, I noticed that error 1222 (lock
timed out) and error 1205 (deadlock...) cannot be trapped directly in
T-SQL. They seem to return control to the client - whatever the client
is and in my particular case, a Java JDBC program using Microsoft's
JDBC drivers.
Is there a way of trapping these errors in T-SQL?
Regards,
z1hou1

Lock Information

I need some help in interpreting the information displayed under
Management -> Current Activity -> Locks / Process ID in Enterprise
Manager.
There seems to be a number of locks against the "Water.dbo.sched_lock"
object. The values in the Index column are different for the different
rows: "Gazetteer", "Employees" and "pk_workbank...".
The strange thing is that these are not indexes of the Sched_Lock
table. Gazetteer and Employees refer to 2 different tables within the
database.
Why are they being displayed against a lock which is supposed to be
held against the "sched_lock" table?No ideas, anyone?|||I never use that to monitor locks. A few years ago there
were posts like yours so I played around with viewing locks
from Enterprise Manager and found that it's pretty easy to
get bogus, useless info. I specifically remember seeing the
wrong table references. And then if you look at the stored
procedure used by Enterprise Manager,
sp_MSset_currency_activity, you can see how the results
wouldn't be too reliable.
Use sp_lock, sp_who2 to monitor things. Or even better is
the stored procedure Erland wrote:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
-Sue
On 7 Sep 2006 09:39:26 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:
>I need some help in interpreting the information displayed under
>Management -> Current Activity -> Locks / Process ID in Enterprise
>Manager.
>There seems to be a number of locks against the "Water.dbo.sched_lock"
>object. The values in the Index column are different for the different
>rows: "Gazetteer", "Employees" and "pk_workbank...".
>The strange thing is that these are not indexes of the Sched_Lock
>table. Gazetteer and Employees refer to 2 different tables within the
>database.
>Why are they being displayed against a lock which is supposed to be
>held against the "sched_lock" table?|||Thanks very much Sue - it's a pain that a part of Enterprise Manager
itself would be unreliable.
I've had a look at sp_MSset_current_activity, and I've seen where the
Index column is likely to get mixed up. I can't see why the Table
column would be unreliable though - it seems like a fair bet to me that
the data in the table column is correct, and the locks are being held
against the "sched_lock" table. Would you agree?|||In your case, yes.
In terms of the other areas of unreliability in that stored
procedure, look at the isolation levels being used.
Many GUI tools for databases are have issues - not just
Microsoft's. You're often better off just knowing the SQL
commands to execute. It generally gives you more flexibility
in writing some of your own scripts to retrieve the data you
want, with the information you need or care about.
-Sue
On 11 Sep 2006 08:21:22 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:
>Thanks very much Sue - it's a pain that a part of Enterprise Manager
>itself would be unreliable.
>I've had a look at sp_MSset_current_activity, and I've seen where the
>Index column is likely to get mixed up. I can't see why the Table
>column would be unreliable though - it seems like a fair bet to me that
>the data in the table column is correct, and the locks are being held
>against the "sched_lock" table. Would you agree?|||Indeed.
Thanks very much.sql

Lock Down Enterprise Manager...

I am looking for any information on locking down EM, such as not allowing
access to certain areas (Security Folder, Management, etc), as well as
securing it so that they cannot even see certain DB's in the Databases
Folder. The security would ideally be tied into their SQL acct info, but NT
perms would work as well.
Can anyone provide some good primer info on this topic? Any hints or advice
would be greatly appreciated.
You can't really. There is no real metadata security in SQL2000 so the
obvious example is that a login with access to only 1 database can see all
databases in EM. Just because they can see stuff in EM doesn't mean they can
do anything. What you can do in EM is all controlled by what permissions you
have in SQL but just because they can see an object in EM doesn't mean they
can do anything to it. If a user has access to only one database they can't
do anything in EM other than in that one database.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rathael1" <Rathael1@.discussions.microsoft.com> wrote in message
news:FA39C12F-ACF6-4465-B464-3C66A53A65E3@.microsoft.com...
>I am looking for any information on locking down EM, such as not allowing
> access to certain areas (Security Folder, Management, etc), as well as
> securing it so that they cannot even see certain DB's in the Databases
> Folder. The security would ideally be tied into their SQL acct info, but
> NT
> perms would work as well.
> Can anyone provide some good primer info on this topic? Any hints or
> advice
> would be greatly appreciated.
|||That is what I was afraid of, and was pretty much what I have discovered
while researching this on my own. The example you just gave is exactly what
we're trying to prevent.
I am looking at some 3rd party solutions (AppSense, etc) that may help, just
in case anyone is running into anything similar. Also, I thought of creating
a web interface to EM, where we could control and filter the content
programatially, but that would be a fairly more involved solution than
getting something off the shelf.
Thanks for your response...if anyone else has any ideas, I'm all ears!
JD, MCSE, MCDBA
"Jasper Smith" wrote:

> You can't really. There is no real metadata security in SQL2000 so the
> obvious example is that a login with access to only 1 database can see all
> databases in EM. Just because they can see stuff in EM doesn't mean they can
> do anything. What you can do in EM is all controlled by what permissions you
> have in SQL but just because they can see an object in EM doesn't mean they
> can do anything to it. If a user has access to only one database they can't
> do anything in EM other than in that one database.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Rathael1" <Rathael1@.discussions.microsoft.com> wrote in message
> news:FA39C12F-ACF6-4465-B464-3C66A53A65E3@.microsoft.com...
>
>
|||What exactly is your concern with the behaviour of EM ? Is this for some
sort of hosting scenario ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rathael1" <rathael1@.discussions.microsoft.com> wrote in message
news:04AFBDCC-4D63-4931-973E-2422E4396A30@.microsoft.com...[vbcol=seagreen]
> That is what I was afraid of, and was pretty much what I have discovered
> while researching this on my own. The example you just gave is exactly
> what
> we're trying to prevent.
> I am looking at some 3rd party solutions (AppSense, etc) that may help,
> just
> in case anyone is running into anything similar. Also, I thought of
> creating
> a web interface to EM, where we could control and filter the content
> programatially, but that would be a fairly more involved solution than
> getting something off the shelf.
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:
|||Have a look at SQL Server Web Data Administrator
http://www.microsoft.com/downloads/d...displaylang=en
This seems to only show databases users have access to. It is extensible and
has documentation on the SqlAdmin class which you can use
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rathael1" <rathael1@.discussions.microsoft.com> wrote in message
news:04AFBDCC-4D63-4931-973E-2422E4396A30@.microsoft.com...[vbcol=seagreen]
> That is what I was afraid of, and was pretty much what I have discovered
> while researching this on my own. The example you just gave is exactly
> what
> we're trying to prevent.
> I am looking at some 3rd party solutions (AppSense, etc) that may help,
> just
> in case anyone is running into anything similar. Also, I thought of
> creating
> a web interface to EM, where we could control and filter the content
> programatially, but that would be a fairly more involved solution than
> getting something off the shelf.
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:
|||rathael1 typed:
> Also, I
> thought of creating a web interface to EM, where we could control and
> filter the content programatially, but that would be a fairly more
> involved solution than getting something off the shelf.
You should have a look at myLittleAdmin on
http://www.mylittletools.net/mla_sql
Live demo on http://www.mylittletools.net/livedemo/mla_sql
You can also download a lite edition
Hope this helps
Best regards
Elian Chrebor
// myLittleTools.net : web-based applications for ASP developers
// myLittleAdmin spotlight is available on
// http://www.mylittletools.net/spotlight
// webmaster@.mylittletools.net
[vbcol=seagreen]
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:

locating sqlexpress database

I have created my sqlexpress database in Visual Studio 2005. When i go to SQL Server Management Studio Express I cannot open my database. It is not listed automatically and I get an error 'There is no editor for 'database name' Make sure the application for file type (.mdf) if installed.'

Any help or suggestions will be appreciated.

Thanks

hmmmm.. When you open sql server management studio are you able to connect to your local sql express 05 instance? Can you see your system tables like Master etc.?

If so, is it possible that you just need to attach to the MDF by right clicking on the local server and selecting "Attach" and then pointing to this new database you have created?

|||

Databases that are created by Visual Studio reside in a per user location and are not attached to the main instance of SQL Express. VS uses a special kind of instance of SQL Express called a User Instance. VS provides a fairly complete set of database management tools built-in, so you should be able to do your management task from directly in VS.

There are a couple barriers if you want to open your database from VS in Management Studio:

VS database are created in a directory that SQL Express doesn't have permissions for, so the file can't be attached. You would need to grant the Network Service account permissions to the project directory where the database file exists.|||

Hey Mike,

Even after granting Network Service account full permissions to all directories, I still could not locate or attatch the dbase, it wouldnt see anything in the folder where it was at.

Regards

Tom

locating sqlexpress database

I have created my sqlexpress database in Visual Studio 2005. When i go to SQL Server Management Studio Express I cannot open my database. It is not listed automatically and I get an error 'There is no editor for 'database name' Make sure the application for file type (.mdf) if installed.'

Any help or suggestions will be appreciated.

Thanks

hmmmm.. When you open sql server management studio are you able to connect to your local sql express 05 instance? Can you see your system tables like Master etc.?

If so, is it possible that you just need to attach to the MDF by right clicking on the local server and selecting "Attach" and then pointing to this new database you have created?

|||

Databases that are created by Visual Studio reside in a per user location and are not attached to the main instance of SQL Express. VS uses a special kind of instance of SQL Express called a User Instance. VS provides a fairly complete set of database management tools built-in, so you should be able to do your management task from directly in VS.

There are a couple barriers if you want to open your database from VS in Management Studio:

VS database are created in a directory that SQL Express doesn't have permissions for, so the file can't be attached. You would need to grant the Network Service account permissions to the project directory where the database file exists.|||

Hey Mike,

Even after granting Network Service account full permissions to all directories, I still could not locate or attatch the dbase, it wouldnt see anything in the folder where it was at.

Regards

Tom

Local Management Studio cannot connect to Remote IS

I am not able to connect my local SQL Server Management Studio to a remote server hosting our Integration Services. I get the infamous "Access Denied". I have walked through Kirk Haselden's howto at:

http://www.sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

but the problem persists.

Thanks for any help.

Keehan

Well, with the help of MS support this has been figured out. The above web link is very nearly complete. The only part that I was missing that was crucial was to go to Component Services on the local machine, right click on My Computer and go to the Default Properties tab. Make sure that "Enable Distributed COM on this computer" is checked. Once I did this I was able to connect right away. So, the moral of the story, check your local DCOM settings as well as those on the server.

Now, one thing to remember, you can connect using Management Studio, but if you try to execute packages they will fail. To run packages you need to also install SSIS from say the Developers Edition on your local machine. Once these 2 things were fixed, I can connect with Management Studio and execute packages via Management Studio.

With both of these changes I am also able now to kickoff jobs via the local dtexec commandline tool.

Cheers,

Keehan

|||

I have the same problem. And, I reviewed/setting based on those documents. I still got the "Access Denied".

However, if the local user have local admin on remote SSIS server, then user will be able connect to the server.

Any input will be helpful.

James Cheng

|||

I overcome this problem by adding local user to "Distributed DCOM Users' group on SSIS server.

Thanks.

|||

Keehan,

As suggested, I have added myself and others in my group to the Distributed COM group on our web server. I still get the error below. The link below appears bad.I am trying to connect using Windows Authentication, as is is the only method available in the Connect dialog. For the Engine, either Windows or SQL Server Authentication works just fine.

---

TITLE: Connect to Server

Cannot connect to ded1368dter.maximumasp.com.

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Connect to SSIS Service on machine "ded1368dter.maximumasp.com" failed:
The RPC server is unavailable.
.

Connect to SSIS Service on machine "ded1368dter.maximumasp.com" failed:
The RPC server is unavailable.
.

BUTTONS:

OK

---

|||

I have the same problem. What's different about our setups that we have to jump through all these hoops ? Did everyone else in the world know to add themselves to the DCOM group ? (which didn't seem to help me anyway). What account do people use on the server for the SQL services ? LocalSystem, or a special windows account ?

When I go into Surface Area Configuration, I notice that "Database Engine" and "Analysis Services" have a sub-tab for "Service" and "Remote Connections". However, "Integration Services" only has a sub-tab for Service, not Remote Connections. Is that OK ?

|||FYI Same problems... tried all the setups including manually adding the group... still "access denied" except for people that are admin on the server.|||I believe that the problem was caused by a bad file of some sort on the CD. We were using the MSDN license CDs and everything else seemed to work. Another guy in the dept had his own MSDN CD he got at a trade show, and his worked fine. When his was installed on the other developers machines, they worked fine as well ........ So maybe others have similar bad CDs ?

Local Management Studio cannot connect to Remote IS

I am not able to connect my local SQL Server Management Studio to a remote server hosting our Integration Services. I get the infamous "Access Denied". I have walked through Kirk Haselden's howto at:

http://www.sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

but the problem persists.

Thanks for any help.

Keehan

Well, with the help of MS support this has been figured out. The above web link is very nearly complete. The only part that I was missing that was crucial was to go to Component Services on the local machine, right click on My Computer and go to the Default Properties tab. Make sure that "Enable Distributed COM on this computer" is checked. Once I did this I was able to connect right away. So, the moral of the story, check your local DCOM settings as well as those on the server.

Now, one thing to remember, you can connect using Management Studio, but if you try to execute packages they will fail. To run packages you need to also install SSIS from say the Developers Edition on your local machine. Once these 2 things were fixed, I can connect with Management Studio and execute packages via Management Studio.

With both of these changes I am also able now to kickoff jobs via the local dtexec commandline tool.

Cheers,

Keehan

|||

I have the same problem. And, I reviewed/setting based on those documents. I still got the "Access Denied".

However, if the local user have local admin on remote SSIS server, then user will be able connect to the server.

Any input will be helpful.

James Cheng

|||

I overcome this problem by adding local user to "Distributed DCOM Users' group on SSIS server.

Thanks.

|||

Keehan,

As suggested, I have added myself and others in my group to the Distributed COM group on our web server. I still get the error below. The link below appears bad.I am trying to connect using Windows Authentication, as is is the only method available in the Connect dialog. For the Engine, either Windows or SQL Server Authentication works just fine.

---

TITLE: Connect to Server

Cannot connect to ded1368dter.maximumasp.com.

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

Connect to SSIS Service on machine "ded1368dter.maximumasp.com" failed:
The RPC server is unavailable.
.

Connect to SSIS Service on machine "ded1368dter.maximumasp.com" failed:
The RPC server is unavailable.
.

BUTTONS:

OK

---

|||

I have the same problem. What's different about our setups that we have to jump through all these hoops ? Did everyone else in the world know to add themselves to the DCOM group ? (which didn't seem to help me anyway). What account do people use on the server for the SQL services ? LocalSystem, or a special windows account ?

When I go into Surface Area Configuration, I notice that "Database Engine" and "Analysis Services" have a sub-tab for "Service" and "Remote Connections". However, "Integration Services" only has a sub-tab for Service, not Remote Connections. Is that OK ?

|||FYI Same problems... tried all the setups including manually adding the group... still "access denied" except for people that are admin on the server.|||I believe that the problem was caused by a bad file of some sort on the CD. We were using the MSDN license CDs and everything else seemed to work. Another guy in the dept had his own MSDN CD he got at a trade show, and his worked fine. When his was installed on the other developers machines, they worked fine as well ........ So maybe others have similar bad CDs ?

Local instance registration on 2005

I just installed the workstation components for SQL 2005 and have opened
Management Studio.
I am trying to register a local instance for testing and am not having any
success. If I select "New" under database engines, I do not have any local
servers to choose from. If I attempt to enter my local computer name for the
server name in New Server Registration properties, it will not connect.
Any idea how to set up a local instance?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1Reading you text I see that you installed the Workstation components and then try to access a local
instance. But the Workstation components doesn't include the database engine...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5da4d493ce717@.uwe...
>I just installed the workstation components for SQL 2005 and have opened
> Management Studio.
> I am trying to register a local instance for testing and am not having any
> success. If I select "New" under database engines, I do not have any local
> servers to choose from. If I attempt to enter my local computer name for the
> server name in New Server Registration properties, it will not connect.
> Any idea how to set up a local instance?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||I have "Database Engine".
In SQL 2K you could register a server as [local].
How is that done in SQL 2005?
Tibor Karaszi wrote:
>Reading you text I see that you installed the Workstation components and then try to access a local
>instance. But the Workstation components doesn't include the database engine...
>>I just installed the workstation components for SQL 2005 and have opened
>> Management Studio.
>[quoted text clipped - 5 lines]
>> Any idea how to set up a local instance?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||cbrichards via SQLMonster.com wrote:
> I have "Database Engine".
> In SQL 2K you could register a server as [local].
> How is that done in SQL 2005?
> Tibor Karaszi wrote:
>>Reading you text I see that you installed the Workstation components and then try to access a local
>>instance. But the Workstation components doesn't include the database engine...
>>
>>I just installed the workstation components for SQL 2005 and have opened
>>Management Studio.
>>[quoted text clipped - 5 lines]
>>Any idea how to set up a local instance?
>
Where do you have "Database Engine"? You can check under services if you
have sqlserver service and if it's running. If it's there it should be
running. If it isn't there, you haven't installed the database engine.
Regards
Steen|||I do have sqlserver service running.
Under registered servers when I right click on [local computer name]\
SQLExpress, then select connect, I get an error saying I cannot connect to
the above instance with "error 26 - Error Locating Server/Instance Specified"
Steen Persson (DK) wrote:
>> I have "Database Engine".
>[quoted text clipped - 11 lines]
>>Any idea how to set up a local instance?
>Where do you have "Database Engine"? You can check under services if you
>have sqlserver service and if it's running. If it's there it should be
>running. If it isn't there, you haven't installed the database engine.
>Regards
>Steen
--
Message posted via http://www.sqlmonster.com|||cbrichards via SQLMonster.com wrote:
> I do have sqlserver service running.
> Under registered servers when I right click on [local computer name]\
> SQLExpress, then select connect, I get an error saying I cannot connect to
> the above instance with "error 26 - Error Locating Server/Instance Specified"
>
> Steen Persson (DK) wrote:
>
Have you tried to go through the SQL Server Configuartion Manager and/or
SQL server Surface Area Configuatio? Maybe there're something you're
missing to setup/enable.
Regards
Steen

local connect to sql express server

I can't connect to the local sql express server. I see in microsoft sql server management studio express that the server is running but I don't fint the server in access 2003. I work with vista.
what can be the reason?

thanks a lot!

Have you verified your connection string?

You might check with http://www.connectionstrings.com/ to determine how to properly create a conneciton string for Access to use SQL Server.

|||

You need to enable the TCP/IP protocol in SQL Server Configuration Manager in order to connect to SQL Server from Access. Access uses ODBC connections, which is only supported via TCP/IP, thus you need to enable that.

Regards,

Mike

|||i enabled the TCP/ip protocol in sql server configuration manager, but i stil can't connect. when I test the connection in access I get the massage that something went wrong with the initialisation of the application.

|||I don't understand "verified your connection string" ... excuse me I'm en newby

|||

You'll need to provied the exact error message otherwise we're just guessing.

For Access to connect to SQL Server you need to have a DSN, which you create using the Create New Data Source wizard. This is launched in Access when you select ODBC as the source for a Linked table. When you filled out the wizard, you were asked to provide the name of the Server and the name of the database, what did you enter for those fields?

Mike

|||I found the solution: I wrote te servername wrong: it had to be: computername\servername

|||

Just in addition and for nitpicking its Computername\Instancename :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de