Cannot remotely login to SQL Express since last windows update

Since today (after installing the latest Windows Updates !on my local machine!) I cannot login to a SQLExpress instance remotely; stating that it cannot find the server/instance.

The weird thing is: it only happens when I want to login to the non-default instance (sqlexpress 2005) (<IP>\<instance>).

When i'm using the default instance (msde 2000 or sqlexpress 2005 on another machine) (<IP>) it seems to work fine.

Does this have something to do with the windows updates, or is it just a coincedence?

Thanks in advance,

Remco RosThis is probably due to the update changed your setting. Just check your firewall on the server and put sqlbrowser.exe or UDP port 1434 to exception. Also make sure your sqlbrowser is running.|||we didn't install the updates on the server (yet) only on the client, so it must be a client thing.

Server:
Win 2003 / SQL Express 2005
Tcp/ip / named pipes: yes
sqlbrowser running
firewall exception on 1434udp 1433tcp.

tested on these client(s):
Local (without latest updates): CAN connect
XP Home with SQL Server Management studio (with lates windows updates): CANNOT connect
XP Pro with SQL Server Management studio Express (with lates windows updates): CANNOT connect
Win 2003 with SQL Server Management studio Express

(WITHOUT lates windows updates): CAN connect.

I see a pattern here...|||Are you sure your SQL Express listens on TCP port 1433 since your put 1433 in exception? What's the result if you run "telnet yourserver sql_tcp_port"? Can it connect? Could it be possible that IPSEC blocked the traffic between the problem machine if the IPSEC is on?

Cannot reinstall Sql Server Management Express

Hi,I just unistalled Sql Management Express and now I want to install it again but the installer says the product is already installed,Can somebody help me with this

What SQL components do you still show in Add/Remove Programs? Can you click on Remove and then on Report to show the detailed list and post the contents back to the thread?

Thanks,
Sam Lester (MSFT)

|||I was trying to reinstall from the setup package, using the change button in the add remove programs utility I was able to reinstall it thanks

Cannot perform requested task because full-text manager is not

I am trying to import/transfer data tables from an existing MS SQL
2005 Express Adv install to an MS SQL 2000 Standard install (on
different boxes) but it is not working. It's seems to be failing with
the above mentioned error. Can anyone help with this?
Thanks,
J.Well, do you have full text indexes on Express Adv.? Did you install/enable
full-text on Standard? You are probably trying to move tables with
full-text indexes onto Standard, but it can't set them up because full text
is not there or not enabled...
<Jeffrey.TOL@.gmail.com> wrote in message
news:dbe41d4f-e3ed-416d-8b27-6a29030b453e@.e39g2000hsf.googlegroups.com...
> I am trying to import/transfer data tables from an existing MS SQL
> 2005 Express Adv install to an MS SQL 2000 Standard install (on
> different boxes) but it is not working. It's seems to be failing with
> the above mentioned error. Can anyone help with this?
> Thanks,
> J.|||Thanks for your reply. I was leaning towards this but was unsure.
Could you tell me how would I go about checking to see if it is
installed on both servers? Is there an SQL query that I can
perform...?
Cheers,
J.
On May 2, 2:48=A0pm, "Aaron Bertrand [SQL Server]"
<ten...@.dnartreb.noraa> wrote:
> Well, do you have full text indexes on Express Adv.? =A0Did you install/en=able
> full-text on Standard? =A0You are probably trying to move tables with
> full-text indexes onto Standard, but it can't set them up because full tex=t
> is not there or not enabled...
> <Jeffrey...@.gmail.com> wrote in message
> news:dbe41d4f-e3ed-416d-8b27-6a29030b453e@.e39g2000hsf.googlegroups.com...
>
> > I am trying to import/transfer data tables from an existing MS SQL
> > 2005 Express Adv install to an MS SQL 2000 Standard install (on
> > different boxes) but it is not working. =A0It's seems to be failing with=
> > the above mentioned error. =A0Can anyone help with this?
> > Thanks,
> > J.- Hide quoted text -
> - Show quoted text -|||> Thanks for your reply. I was leaning towards this but was unsure.
> Could you tell me how would I go about checking to see if it is
> installed on both servers? Is there an SQL query that I can
> perform...?
I would check the services applet in the control panel.|||Yup looked there but it didn't say much, so I tried a reinstall and
the option was there but unselected. Once the install went through,
all was well. I am copying the data now. Thanks!!!
Cheers,
J.
On May 2, 3:10=A0pm, "Aaron Bertrand [SQL Server]"
<ten...@.dnartreb.noraa> wrote:
> > Thanks for your reply. =A0I was leaning towards this but was unsure.
> > Could you tell me how would I go about checking to see if it is
> > installed on both servers? =A0Is there an SQL query that I can
> > perform...?
> I would check the services applet in the control panel.|||Jeffrey,
If you do not need the full text indexes, I suppose that you could go
through your databases (there are only a few, right) and try to drop all
full-text indexes.
Look under:
DatabaseName
Storage
Full Text Catalogs
From the catalogs you can determine which tables have full text indexes. If
you script a drop of the catalog it will script out a drop the indexes then
drop the catalogs. I have not tested this, but it is a free idea. If it
seems suitable, try it out and see if solves your problem.
RLF
<Jeffrey.TOL@.gmail.com> wrote in message
news:a3e0cfaf-2fef-47c9-af25-9a72bf3499d5@.y38g2000hsy.googlegroups.com...
Thanks for your reply. I was leaning towards this but was unsure.
Could you tell me how would I go about checking to see if it is
installed on both servers? Is there an SQL query that I can
perform...?
Cheers,
J.
On May 2, 2:48 pm, "Aaron Bertrand [SQL Server]"
<ten...@.dnartreb.noraa> wrote:
> Well, do you have full text indexes on Express Adv.? Did you
> install/enable
> full-text on Standard? You are probably trying to move tables with
> full-text indexes onto Standard, but it can't set them up because full
> text
> is not there or not enabled...
> <Jeffrey...@.gmail.com> wrote in message
> news:dbe41d4f-e3ed-416d-8b27-6a29030b453e@.e39g2000hsf.googlegroups.com...
>
> > I am trying to import/transfer data tables from an existing MS SQL
> > 2005 Express Adv install to an MS SQL 2000 Standard install (on
> > different boxes) but it is not working. It's seems to be failing with
> > the above mentioned error. Can anyone help with this?
> > Thanks,
> > J.- Hide quoted text -
> - Show quoted text -|||Well, the reinstall was a better idea. Congrats. - RLF
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23Ag4UyIrIHA.524@.TK2MSFTNGP05.phx.gbl...
> Jeffrey,
> If you do not need the full text indexes, I suppose that you could go
> through your databases (there are only a few, right) and try to drop all
> full-text indexes.
> Look under:
> DatabaseName
> Storage
> Full Text Catalogs
> From the catalogs you can determine which tables have full text indexes.
> If you script a drop of the catalog it will script out a drop the indexes
> then drop the catalogs. I have not tested this, but it is a free idea. If
> it seems suitable, try it out and see if solves your problem.
> RLF
> <Jeffrey.TOL@.gmail.com> wrote in message
> news:a3e0cfaf-2fef-47c9-af25-9a72bf3499d5@.y38g2000hsy.googlegroups.com...
> Thanks for your reply. I was leaning towards this but was unsure.
> Could you tell me how would I go about checking to see if it is
> installed on both servers? Is there an SQL query that I can
> perform...?
> Cheers,
> J.
>
>
> On May 2, 2:48 pm, "Aaron Bertrand [SQL Server]"
> <ten...@.dnartreb.noraa> wrote:
>> Well, do you have full text indexes on Express Adv.? Did you
>> install/enable
>> full-text on Standard? You are probably trying to move tables with
>> full-text indexes onto Standard, but it can't set them up because full
>> text
>> is not there or not enabled...
>> <Jeffrey...@.gmail.com> wrote in message
>> news:dbe41d4f-e3ed-416d-8b27-6a29030b453e@.e39g2000hsf.googlegroups.com...
>>
>> > I am trying to import/transfer data tables from an existing MS SQL
>> > 2005 Express Adv install to an MS SQL 2000 Standard install (on
>> > different boxes) but it is not working. It's seems to be failing with
>> > the above mentioned error. Can anyone help with this?
>> > Thanks,
>> > J.- Hide quoted text -
>> - Show quoted text -
>

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

http://msdn2.microsoft.com/en-us/library/aa302377.aspx

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

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: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

http://msdn2.microsoft.com/en-us/library/aa302377.aspx

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

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: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

http://msdn2.microsoft.com/en-us/library/aa302377.aspx

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

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: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot open user database error message AFTER I connect and log in.

Ok so I'm using the Management Studio Express and was able to log in and get into the database server, but I had to click the "options>>" button and change database I wanted to connect to from <<default>> to my database on the server or else I get the "cannot open user database, login failed" error message.

Then I right click my database and it gives me the "Cannot open user database. Login failed" error message, even though I already connected to the database to login during the connect screen.

Also I'm able to interact with the database using SQL QUERIES in the Management Studio, like adding new tables with it, but if I right click the database and click "add table" I get that error.

What gives? I know I have permission since I was able to log on, and modify the database using SQL queries but if use the GUI I can't use it. I can't even right-click select "properties" without getting that error.

The reason I want to use the GUI is because I want to right click and use the backup feature of the database.

My guess is that Management Studio continues to point to the default database(which gives me that message) after I already logged in, so I have no idea how to change the default database that management studio points to, to the one I do have permission to use.

Thanks in advance.

What's the account you used to connect to SQL Database Engine in Management Studio Express? The 'Default Database' in SQL is a property of login, that means each login has a Default Database which will be accessed when connecting to SQL Server. You can check this property in the Security->login Properties. Just make sure your login has access on its default database.

cannot open table in sql server management studio express

Hello all.

I have uploaded a table into sql management studio express. However, when I right click on the table and try and open it, I get an error message saying;

"SQL Execution Error.

Executed SQL statement: select columnName1, columnName2 etc....

Error source: Microsoft. VisualStudio.DataTools

Error Message: Exception has been thrown by the target of an invocation"

Because of this error, I cannot manually edit the table. However, when I write a query running select * from Table X, the table does appear that way.

Any help regarding how to open the table would be very much appreciated!!

I have same issue now. What is your solution?|||

Hi,

If you wanna modify data. you'd rather build a T-SQL script (e.g. with insert, update and/or delete
statements).

To prevent problems also consider enclosing the modification in a transaction, e.g.:

begin tran

-- do modifications

-- verify results

commit tran -- if results were ok

rollback tran -- if results were wrong

BTW, the question may be asked better at Sql server express :)

cannot open table in sql server management studio express

Hello all.

I have uploaded a table into sql management studio express. However, when I right click on the table and try and open it, I get an error message saying;

"SQL Execution Error.

Executed SQL statement: select columnName1, columnName2 etc....

Error source: Microsoft. VisualStudio.DataTools

Error Message: Exception has been thrown by the target of an invocation"

Because of this error, I cannot manually edit the table. However, when I write a query running select * from Table X, the table does appear that way.

Any help regarding how to open the table would be very much appreciated!!

I have same issue now. What is your solution?|||

Hi,

If you wanna modify data. you'd rather build a T-SQL script (e.g. with insert, update and/or delete
statements).

To prevent problems also consider enclosing the modification in a transaction, e.g.:

begin tran

-- do modifications

-- verify results

commit tran -- if results were ok

rollback tran -- if results were wrong

BTW, the question may be asked better at Sql server express :)

cannot open table in sql server management studio express

Hello all.

I have uploaded a table into sql management studio express. However, when I right click on the table and try and open it, I get an error message saying;

"SQL Execution Error.

Executed SQL statement: select columnName1, columnName2 etc....

Error source: Microsoft. VisualStudio.DataTools

Error Message: Exception has been thrown by the target of an invocation"

Because of this error, I cannot manually edit the table. However, when I write a query running select * from Table X, the table does appear that way.

Any help regarding how to open the table would be very much appreciated!!

I have same issue now. What is your solution?|||

Hi,

If you wanna modify data. you'd rather build a T-SQL script (e.g. with insert, update and/or delete
statements).

To prevent problems also consider enclosing the modification in a transaction, e.g.:

begin tran

-- do modifications

-- verify results

commit tran -- if results were ok

rollback tran -- if results were wrong

BTW, the question may be asked better at Sql server express :)

sql

Cannot open database"..."requested by the login...??

Hello,

I am using Visual Web Developer 2005 Express Edition and I am following the book:"Beginning ASP.NET 2.0 E-commerce in C# . I am encountering a problem and I haven't got the slightest idea how to solve the problem.

(If you have the book I got to p98)

If I debug then I get the next error: cannot open database"..." requested by the login...

In the book I had to make a GenericDataAccess.cs. What follows are snippets where the error occurs.

public static DataTable ExecuteSelectCommand(DbCommand command)

{

DataTable table

try

{

command.Connection.Open();

DbDataReader reader = command.ExecuteReader();

table = new DataTable();

table.Load(reader);

reader.Close();

}

catch (Exception ex)

{

Utilities.LogError(ex);

throw ex; //this is where my error occurs when I debug it

}

finally

{

commandConnection.close();

}

return table;

}

Ideas and sollutions are more than welcome :-)

YES!!!

I'm currently using the same book and having the same problem too!

May i ask if how did you bypass the SQL Express part? Which programs did you use to substitute SQL Express Manager? I'm having problems with my database connection.

Would appreciate if you can share.

Thanks!

Cannot open database during merge replication

Hi All,
I am replicating an SQL2005 express machine to SQL2005 standard edition server using merge replication.

I have a simple VB application using ADO polling msMerge_history at the subscriber every second so that i can show the end user the progress of the replication.

When the subscription first subscribes and after the initial snapshot is applied replication downloads all changes from the server that have been changed since the snapshot was created.

Towards the end of this download faze the VB polling application fails with "Cannot open database "DBName" requested by the login. The login failed."

This connection fails 4 or 5 times and then normal polling resumes. The scary thing is that sometimes when the polling connection fails the merge replication does not complete and if i check msMerge_Sessions the "runstatus" is stuck at 3 (InProgress).

1) Is the reason the ADO polling connection fails because of some sort of lock between replication and reading the system tables?
2) Is replication getting stuck because of locks?
3) What can i do to get around this?

cheers,
Tim

Hello Tim,

Please try to use uncommited read isolation level (or SELECT ... WITH (NOLOCK) )when querying msMerge_Sessions table.

But result row set from this query contains uncommitted transactions.

This posting is provided AS IS with no warranties, and confers no rights

Cannot Open Database after Publishing

Hi All,

I have developed a database application using c# and SQL Express 2005.

I have coded a connection string that works fine on the development machine but upon publishing to another machine I am getting the following error message:

Cannot open database "name of database" requested by the login. The login failed. Login Failed for user "machine name\user name".

The remote machine has SQL server express installed.

Now on the development machine the machine name and user names are different to that on the remote machine. I am using Windows Auth. The application successfully runs on the remote machine. It even updates the same database (as mentioned in the issue above) on a different form which all connection strings etc were created automatically by c#. I'm pretty new to all this and don't know where to go from here.
Any help would be greatly appritiated.

Thanks
Jon.

Hi,

The error message says that you have Windows Authentication , change it to SQL/Mix Authentication and your application will run.

Refer below links

http://support.microsoft.com/kb/555332

http://hemantgirisgoswami.blogspot.com/2006/11/change-authentication-mode-with.html

And also refer How to enable Remote Connection

blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/kb/914277/en-us

Hemantgiri S. Goswami

|||Thanks. I will give that a try.|||Hi Hemantgiri,

Thanks for the reply. Do I need to change this in SQL Man Studio Express or in my application?

Sorry, I'm new to this

Thanks

Jon|||

Hi,

You need to change this in your SQL Server Properties using SQL Server Management Studio.

1. Open Your SQL Server Management Studio

2. Connect / Login to Server

3. Right Click on the Server Name

4. Click on Properties

5. Select Security

6. Select the option "SQL Server and Windows Authentication Mode" under Server Authentication

7. Click Ok

Hemantgiri S. Goswami

|||Thanks

Cannot open database "testDB" requested by the login. The login failed.

I have created a new database within SQLServer Express 2005 and have an ASP page in IIS. Any time i attempt to access the database I get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "testDB" requested by the login. The login failed.

The ODBC connection that I have created works fine if connecting to the same table (literally three sample fields with two records) in an Access database.

The database connection that I am using is:

Set DatabaseConnection = Server.CreateObject("ADODB.Connection")
DatabaseConnection.Open "DSN=testDB;UID=EINSTEIN\Administrator&pwd=password;DATABASE=testDB;APP=ASP Script"
Set rs = Server.CreateObject("ADODB.recordset")
SQLString = "SELECT * FROM testTable"
rs.Open SQLString, DatabaseConnection
if NOT rs.EOF then
Response.Write(rs("testField1") & " - " & rs("testField2"))
end if
rs.Close
DatabaseConnection.Close
Set DatabaseConnection = Nothing

EINSTEIN is the name of the server, rs = Recordset. I have tried changing to just Administrator instead of machinename\Administrator with no success. I have got SQL Server Management Studio Express CTP, and can log in fine to SQL Server using EINSTEIN\Administrator.

Please can anyone help!

Suggesting that you are going the DSN-less way (Connectionstrings can be found here www.connectionstrings.com) the database which is requested is not available for this user. You cannot provide a Windows User name if you want to use trusted authentication (it some time ago, that I used DSN, but I guess this is just possible for passing over SQL Server authentication. Try using a DSN-less connection with specifying a database that is accessible for the user.

See if that works, Jens Suessmeyer.

|||

It looks like your connection string may be a little incorrect, the current string is:

"DSN=testDB;UID=EINSTEIN\Administrator&pwd=password;DATABASE=testDB;APP=ASP Script"

It should be

"DSN=testDB;UID=EINSTEIN\Administrator;pwd=password;DATABASE=testDB;APP=ASP Script"

No Ampersand between Administrator&pwd, instead it should be a semi-colon

See if that works

Cannot open database "mydb" requested by the login.

Hi,
I installed sqlserver 2005 express on windows server 2000 sp4.
I made a new database ("mydb") with sql server Management studio express
(not a mdf file).
I also defined tables and i can make and execute queries in sql server
Management studio express without problem.
I granted to account ASPNET all privileges on the directories of sql server
(read, write, modify..) in c:\program files\sql server.
But when i try to execute this aspx-code (aspnet 2.0) with IE,
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:mydbConnectionString %>"
SelectCommand="select col1 from table1;"></asp:SqlDataSource>
i get this error:
Cannot open database "madb" requested by the login. The login failed.
Login failed for user 'PEN1800\ASPNET'.
How to solve this?
Thanks
CasTo grant access to a SQL Server instance (SQL Express or
other versions), you need to add the account as a login in
SQL Server. You don't need to mess with permissions on the
database files or in the SQL Server directory.
Then to allow that login access to a database, you need to
add the login as a user in the database. Once you have done
that, you need to grant the whatever permissions are
necessary in the database.
Add the login PEN1800\ASPNET to your SQL Express instance.
Add PEN1800\ASPNET as a user to the database mydb
Grant whatever permissions are needed for PEN1800\ASPNET
-Sue
On Fri, 5 May 2006 21:30:38 +0200, "cas" <cas@.nomail> wrote:

>Hi,
>I installed sqlserver 2005 express on windows server 2000 sp4.
>I made a new database ("mydb") with sql server Management studio express
>(not a mdf file).
>I also defined tables and i can make and execute queries in sql server
>Management studio express without problem.
>I granted to account ASPNET all privileges on the directories of sql server
>(read, write, modify..) in c:\program files\sql server.
>But when i try to execute this aspx-code (aspnet 2.0) with IE,
><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
>ConnectionStrings:mydbConnectionString %>"
> SelectCommand="select col1 from table1;"></asp:SqlDataSource>
> i get this error:
>Cannot open database "madb" requested by the login. The login failed.
>Login failed for user 'PEN1800\ASPNET'.
>How to solve this?
>Thanks
>Cas
>|||Hi,
thanks for replying.
I'm new to this so sorry if it's a stupid question, but can i add the login
PEN1800\ASPNET to the SQL Express instance with 'sqlserver Management Studio
express' or i have to use sqlcmd and the command 'create login ..'.?
And about adding PEN1800\ASPNET as a user to the database mydb, can i do
that with 'sqlserver Management Studio express'?
Thanks
Cas
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nlen525khvamen0l1viflf84g5j0lbjplg@.
4ax.com...
> To grant access to a SQL Server instance (SQL Express or
> other versions), you need to add the account as a login in
> SQL Server. You don't need to mess with permissions on the
> database files or in the SQL Server directory.
> Then to allow that login access to a database, you need to
> add the login as a user in the database. Once you have done
> that, you need to grant the whatever permissions are
> necessary in the database.
> Add the login PEN1800\ASPNET to your SQL Express instance.
> Add PEN1800\ASPNET as a user to the database mydb
> Grant whatever permissions are needed for PEN1800\ASPNET
> -Sue
> On Fri, 5 May 2006 21:30:38 +0200, "cas" <cas@.nomail> wrote:
>
server[vbcol=seagreen]
ConnectionString="<%$[vbcol=seagreen]
>|||I don't know what you can and can't do with Management
Studio Express. When in doubt, just use sqlcmd as you can
certainly do it with t-sql statement.
-Sue
On Sat, 6 May 2006 10:15:58 +0200, "cas" <cas@.nomail> wrote:

>Hi,
>thanks for replying.
>I'm new to this so sorry if it's a stupid question, but can i add the login
>PEN1800\ASPNET to the SQL Express instance with 'sqlserver Management Studi
o
>express' or i have to use sqlcmd and the command 'create login ..'.?
>And about adding PEN1800\ASPNET as a user to the database mydb, can i do
>that with 'sqlserver Management Studio express'?
> Thanks
>Cas
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:nlen525khvamen0l1viflf84g5j0lbjplg@.
4ax.com...
>server
>ConnectionString="<%$
>|||In Management Studio Express, look at the Security Logins folder. Add ASPNE
T
and set permissions in the USER MAPPING section. Since the webpage reads an
d
writes, I selected db_datareader and db_datawriter (and public, but not sure
this is wise for non-intranet pages?). Hope this helps get you in the area.
"Sue Hoegemeier" wrote:

> I don't know what you can and can't do with Management
> Studio Express. When in doubt, just use sqlcmd as you can
> certainly do it with t-sql statement.
> -Sue
> On Sat, 6 May 2006 10:15:58 +0200, "cas" <cas@.nomail> wrote:
>
>

Cannot open database "mydb" requested by the login.

Hi,
I installed sqlserver 2005 express on windows server 2000 sp4.
I made a new database ("mydb") with sql server Management studio express
(not a mdf file).
I also defined tables and i can make and execute queries in sql server
Management studio express without problem.
I granted to account ASPNET all privileges on the directories of sql server
(read, write, modify..) in c:\program files\sql server.
But when i try to execute this aspx-code (aspnet 2.0) with IE,
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:mydbConnectionString %>"
SelectCommand="select col1 from table1;"></asp:SqlDataSource>
i get this error:
Cannot open database "madb" requested by the login. The login failed.
Login failed for user 'PEN1800\ASPNET'.
How to solve this?
Thanks
CasTo grant access to a SQL Server instance (SQL Express or
other versions), you need to add the account as a login in
SQL Server. You don't need to mess with permissions on the
database files or in the SQL Server directory.
Then to allow that login access to a database, you need to
add the login as a user in the database. Once you have done
that, you need to grant the whatever permissions are
necessary in the database.
Add the login PEN1800\ASPNET to your SQL Express instance.
Add PEN1800\ASPNET as a user to the database mydb
Grant whatever permissions are needed for PEN1800\ASPNET
-Sue
On Fri, 5 May 2006 21:30:38 +0200, "cas" <cas@.nomail> wrote:
>Hi,
>I installed sqlserver 2005 express on windows server 2000 sp4.
>I made a new database ("mydb") with sql server Management studio express
>(not a mdf file).
>I also defined tables and i can make and execute queries in sql server
>Management studio express without problem.
>I granted to account ASPNET all privileges on the directories of sql server
>(read, write, modify..) in c:\program files\sql server.
>But when i try to execute this aspx-code (aspnet 2.0) with IE,
><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
>ConnectionStrings:mydbConnectionString %>"
> SelectCommand="select col1 from table1;"></asp:SqlDataSource>
> i get this error:
>Cannot open database "madb" requested by the login. The login failed.
>Login failed for user 'PEN1800\ASPNET'.
>How to solve this?
>Thanks
>Cas
>|||Hi,
thanks for replying.
I'm new to this so sorry if it's a stupid question, but can i add the login
PEN1800\ASPNET to the SQL Express instance with 'sqlserver Management Studio
express' or i have to use sqlcmd and the command 'create login ..'.?
And about adding PEN1800\ASPNET as a user to the database mydb, can i do
that with 'sqlserver Management Studio express'?
Thanks
Cas
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nlen525khvamen0l1viflf84g5j0lbjplg@.4ax.com...
> To grant access to a SQL Server instance (SQL Express or
> other versions), you need to add the account as a login in
> SQL Server. You don't need to mess with permissions on the
> database files or in the SQL Server directory.
> Then to allow that login access to a database, you need to
> add the login as a user in the database. Once you have done
> that, you need to grant the whatever permissions are
> necessary in the database.
> Add the login PEN1800\ASPNET to your SQL Express instance.
> Add PEN1800\ASPNET as a user to the database mydb
> Grant whatever permissions are needed for PEN1800\ASPNET
> -Sue
> On Fri, 5 May 2006 21:30:38 +0200, "cas" <cas@.nomail> wrote:
> >Hi,
> >
> >I installed sqlserver 2005 express on windows server 2000 sp4.
> >
> >I made a new database ("mydb") with sql server Management studio express
> >(not a mdf file).
> >I also defined tables and i can make and execute queries in sql server
> >Management studio express without problem.
> >I granted to account ASPNET all privileges on the directories of sql
server
> >(read, write, modify..) in c:\program files\sql server.
> >
> >But when i try to execute this aspx-code (aspnet 2.0) with IE,
> ><asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
> >ConnectionStrings:mydbConnectionString %>"
> > SelectCommand="select col1 from table1;"></asp:SqlDataSource>
> >
> > i get this error:
> >Cannot open database "madb" requested by the login. The login failed.
> >Login failed for user 'PEN1800\ASPNET'.
> >How to solve this?
> >Thanks
> >Cas
> >
>|||I don't know what you can and can't do with Management
Studio Express. When in doubt, just use sqlcmd as you can
certainly do it with t-sql statement.
-Sue
On Sat, 6 May 2006 10:15:58 +0200, "cas" <cas@.nomail> wrote:
>Hi,
>thanks for replying.
>I'm new to this so sorry if it's a stupid question, but can i add the login
>PEN1800\ASPNET to the SQL Express instance with 'sqlserver Management Studio
>express' or i have to use sqlcmd and the command 'create login ..'.?
>And about adding PEN1800\ASPNET as a user to the database mydb, can i do
>that with 'sqlserver Management Studio express'?
> Thanks
>Cas
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:nlen525khvamen0l1viflf84g5j0lbjplg@.4ax.com...
>> To grant access to a SQL Server instance (SQL Express or
>> other versions), you need to add the account as a login in
>> SQL Server. You don't need to mess with permissions on the
>> database files or in the SQL Server directory.
>> Then to allow that login access to a database, you need to
>> add the login as a user in the database. Once you have done
>> that, you need to grant the whatever permissions are
>> necessary in the database.
>> Add the login PEN1800\ASPNET to your SQL Express instance.
>> Add PEN1800\ASPNET as a user to the database mydb
>> Grant whatever permissions are needed for PEN1800\ASPNET
>> -Sue
>> On Fri, 5 May 2006 21:30:38 +0200, "cas" <cas@.nomail> wrote:
>> >Hi,
>> >
>> >I installed sqlserver 2005 express on windows server 2000 sp4.
>> >
>> >I made a new database ("mydb") with sql server Management studio express
>> >(not a mdf file).
>> >I also defined tables and i can make and execute queries in sql server
>> >Management studio express without problem.
>> >I granted to account ASPNET all privileges on the directories of sql
>server
>> >(read, write, modify..) in c:\program files\sql server.
>> >
>> >But when i try to execute this aspx-code (aspnet 2.0) with IE,
>> ><asp:SqlDataSource ID="SqlDataSource1" runat="server"
>ConnectionString="<%$
>> >ConnectionStrings:mydbConnectionString %>"
>> > SelectCommand="select col1 from table1;"></asp:SqlDataSource>
>> >
>> > i get this error:
>> >Cannot open database "madb" requested by the login. The login failed.
>> >Login failed for user 'PEN1800\ASPNET'.
>> >How to solve this?
>> >Thanks
>> >Cas
>> >
>|||In Management Studio Express, look at the Security Logins folder. Add ASPNET
and set permissions in the USER MAPPING section. Since the webpage reads and
writes, I selected db_datareader and db_datawriter (and public, but not sure
this is wise for non-intranet pages?). Hope this helps get you in the area.
"Sue Hoegemeier" wrote:
> I don't know what you can and can't do with Management
> Studio Express. When in doubt, just use sqlcmd as you can
> certainly do it with t-sql statement.
> -Sue
> On Sat, 6 May 2006 10:15:58 +0200, "cas" <cas@.nomail> wrote:
> >Hi,
> >thanks for replying.
> >I'm new to this so sorry if it's a stupid question, but can i add the login
> >PEN1800\ASPNET to the SQL Express instance with 'sqlserver Management Studio
> >express' or i have to use sqlcmd and the command 'create login ..'.?
> >And about adding PEN1800\ASPNET as a user to the database mydb, can i do
> >that with 'sqlserver Management Studio express'?
> > Thanks
> >Cas
> >
> >
> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >news:nlen525khvamen0l1viflf84g5j0lbjplg@.4ax.com...
> >> To grant access to a SQL Server instance (SQL Express or
> >> other versions), you need to add the account as a login in
> >> SQL Server. You don't need to mess with permissions on the
> >> database files or in the SQL Server directory.
> >> Then to allow that login access to a database, you need to
> >> add the login as a user in the database. Once you have done
> >> that, you need to grant the whatever permissions are
> >> necessary in the database.
> >> Add the login PEN1800\ASPNET to your SQL Express instance.
> >> Add PEN1800\ASPNET as a user to the database mydb
> >> Grant whatever permissions are needed for PEN1800\ASPNET
> >>
> >> -Sue
> >>
> >> On Fri, 5 May 2006 21:30:38 +0200, "cas" <cas@.nomail> wrote:
> >>
> >> >Hi,
> >> >
> >> >I installed sqlserver 2005 express on windows server 2000 sp4.
> >> >
> >> >I made a new database ("mydb") with sql server Management studio express
> >> >(not a mdf file).
> >> >I also defined tables and i can make and execute queries in sql server
> >> >Management studio express without problem.
> >> >I granted to account ASPNET all privileges on the directories of sql
> >server
> >> >(read, write, modify..) in c:\program files\sql server.
> >> >
> >> >But when i try to execute this aspx-code (aspnet 2.0) with IE,
> >> ><asp:SqlDataSource ID="SqlDataSource1" runat="server"
> >ConnectionString="<%$
> >> >ConnectionStrings:mydbConnectionString %>"
> >> > SelectCommand="select col1 from table1;"></asp:SqlDataSource>
> >> >
> >> > i get this error:
> >> >Cannot open database "madb" requested by the login. The login failed.
> >> >Login failed for user 'PEN1800\ASPNET'.
> >> >How to solve this?
> >> >Thanks
> >> >Cas
> >> >
> >>
> >
>

Cannot Open / Create SQL Express Databases (.mdf files) in Visual Studio 2005 Professional

Hi, I am trying to open or create a sqlexpress database within Visual Studio 2005 Professional in the App_Data folder. If I attempt either method, I get the following dialog box:

Required Components Missing

Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkID=49251

The link above just takes you to the download page for Sql Server Express 2005.

I have both SQL Server 2005 Developer and SQL Server 2005 Expression instances running. The existing database will work properly in my web application, however I cannot access it through Visual Studio.

As background, I did have a problem connecting to the database via the web application, receiving a "Failed to generate a User Instance of SQL Server" error. But I was able to fix that by renaming my SQLEXPRESS folder in C:\Documents and Settings\user\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data. When the web page ran, it created another SQLEXPRESS folder.

However, I cannot figure out why I am having the other issue.

Thanks in advance

Patrick

Hi,

This error means that you are not pointing to the correct instance.

Right click the mdf database > Modify Connection > Advanced > Data Source string.

Check this string and see if it points to the correct instance

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Cannot make remote connection to SQL Server Express

I'm having trouble making a remote connection to a SQL Server Express
database. The service is started, and the connection works locally. I
tested it using a .udl. In this local .udl, I use the SQL Native
Client as the provider, and a Data Source entry of (local)\SQLEXPRESS,
with username/password log on, and initial catalog.
Other Data Source entries that work are .\SQLEXPRESS and LOTSAYOWSAWE
\SQLEXPRESS. The latter entry is a mock-up of the web server's name,
which is 15 characters long, the last 3 being "WEB". The 'B' is
truncated from the server name.
All these connections work locally on the web server (where SQL
Express is running).
The network administrator has opened TCP port 1433 on the web server.
The service has been configured to allow remote connections on TCP/IP
and Named Pipes.

>From the remote machine, the same .udl is set up, but with a Data
Source modified to use an IP address. The Data Source entry then looks
like 152.21.110.58\SQLEXPRESS. I test the connection and get the error
"Test connection failed because of an error initializing provider.
Login timeout expired."
The network administrator reports attempts to communicate with
152.21.110.58 on port 1433. These attempts are being allowed.
I suspect the data source is the problem. Some documentation indicates
port 1433 must be explicitly stated when connecting remotely to SQL
Server 2005 and its variants (including Express). However, I can't
find any examples of explicit port designation in a .udl.
The alternative to explicit port connection (according to some
sources) is to open port 1434 for SQL Browser. That port was opened
and SQL Browser service started, but the connection failed in the same
fashion.
The application that needs to connect is written in C#/.NET.
Can the .udl be configured to test the remote connection? The
initiator of the connection is an Oracle box, so SQLCMD is not an
option for testing remote connections.
Is there something amiss with the Data Source designation? Everything
I read points to replacing server specs like '.' and '(local)' with IP
designations like 152.21.110.68, and keeping the '\SQLEXPRESS' part.
Is this correct? The SQL Server is a web server in a DMZ, so DNS
translation is not available.
How do I monitor SQL Express for login attempts, both local and
remote?
Thanks,
Dan
Hi Dan
"dan@.wagers.net" wrote:

> I'm having trouble making a remote connection to a SQL Server Express
> database. The service is started, and the connection works locally. I
> tested it using a .udl. In this local .udl, I use the SQL Native
> Client as the provider, and a Data Source entry of (local)\SQLEXPRESS,
> with username/password log on, and initial catalog.
> Other Data Source entries that work are .\SQLEXPRESS and LOTSAYOWSAWE
> \SQLEXPRESS. The latter entry is a mock-up of the web server's name,
> which is 15 characters long, the last 3 being "WEB". The 'B' is
> truncated from the server name.
> All these connections work locally on the web server (where SQL
> Express is running).
> The network administrator has opened TCP port 1433 on the web server.
> The service has been configured to allow remote connections on TCP/IP
> and Named Pipes.
> Source modified to use an IP address. The Data Source entry then looks
> like 152.21.110.58\SQLEXPRESS. I test the connection and get the error
> "Test connection failed because of an error initializing provider.
> Login timeout expired."
> The network administrator reports attempts to communicate with
> 152.21.110.58 on port 1433. These attempts are being allowed.
> I suspect the data source is the problem. Some documentation indicates
> port 1433 must be explicitly stated when connecting remotely to SQL
> Server 2005 and its variants (including Express). However, I can't
> find any examples of explicit port designation in a .udl.
> The alternative to explicit port connection (according to some
> sources) is to open port 1434 for SQL Browser. That port was opened
> and SQL Browser service started, but the connection failed in the same
> fashion.
> The application that needs to connect is written in C#/.NET.
> Can the .udl be configured to test the remote connection? The
> initiator of the connection is an Oracle box, so SQLCMD is not an
> option for testing remote connections.
> Is there something amiss with the Data Source designation? Everything
> I read points to replacing server specs like '.' and '(local)' with IP
> designations like 152.21.110.68, and keeping the '\SQLEXPRESS' part.
> Is this correct? The SQL Server is a web server in a DMZ, so DNS
> translation is not available.
> How do I monitor SQL Express for login attempts, both local and
> remote?
> Thanks,
> Dan
>
By default SQLExpress does not have network protocols enabled by default see
http://msdn2.microsoft.com/en-us/library/ms190198.aspx ,
http://msdn2.microsoft.com/en-us/library/ms143446.aspx
and
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#disablenetworkprotocols
Using the command line installation option of DISABLENETWORKPROTOCOLS with
values of 0 or 2 will enable Shared Memory and TCP/IP (0 also enables named
pipes)
If this has not be done you can still enable remote connections after you
have installed SQLExpress by using the Surface Area Configuration Tool
(SqlSAC.exe)
to enable remote connections see
http://msdn2.microsoft.com/en-us/library/ms173748.aspx
John

Cannot make remote connection to SQL Server Express

I'm having trouble making a remote connection to a SQL Server Express
database. The service is started, and the connection works locally. I
tested it using a .udl. In this local .udl, I use the SQL Native
Client as the provider, and a Data Source entry of (local)\SQLEXPRESS,
with username/password log on, and initial catalog.
Other Data Source entries that work are .\SQLEXPRESS and LOTSAYOWSAWE
\SQLEXPRESS. The latter entry is a mock-up of the web server's name,
which is 15 characters long, the last 3 being "WEB". The 'B' is
truncated from the server name.
All these connections work locally on the web server (where SQL
Express is running).
The network administrator has opened TCP port 1433 on the web server.
The service has been configured to allow remote connections on TCP/IP
and Named Pipes.
>From the remote machine, the same .udl is set up, but with a Data
Source modified to use an IP address. The Data Source entry then looks
like 152.21.110.58\SQLEXPRESS. I test the connection and get the error
"Test connection failed because of an error initializing provider.
Login timeout expired."
The network administrator reports attempts to communicate with
152.21.110.58 on port 1433. These attempts are being allowed.
I suspect the data source is the problem. Some documentation indicates
port 1433 must be explicitly stated when connecting remotely to SQL
Server 2005 and its variants (including Express). However, I can't
find any examples of explicit port designation in a .udl.
The alternative to explicit port connection (according to some
sources) is to open port 1434 for SQL Browser. That port was opened
and SQL Browser service started, but the connection failed in the same
fashion.
The application that needs to connect is written in C#/.NET.
Can the .udl be configured to test the remote connection? The
initiator of the connection is an Oracle box, so SQLCMD is not an
option for testing remote connections.
Is there something amiss with the Data Source designation? Everything
I read points to replacing server specs like '.' and '(local)' with IP
designations like 152.21.110.68, and keeping the '\SQLEXPRESS' part.
Is this correct? The SQL Server is a web server in a DMZ, so DNS
translation is not available.
How do I monitor SQL Express for login attempts, both local and
remote?
Thanks,
DanHi Dan
"dan@.wagers.net" wrote:
> I'm having trouble making a remote connection to a SQL Server Express
> database. The service is started, and the connection works locally. I
> tested it using a .udl. In this local .udl, I use the SQL Native
> Client as the provider, and a Data Source entry of (local)\SQLEXPRESS,
> with username/password log on, and initial catalog.
> Other Data Source entries that work are .\SQLEXPRESS and LOTSAYOWSAWE
> \SQLEXPRESS. The latter entry is a mock-up of the web server's name,
> which is 15 characters long, the last 3 being "WEB". The 'B' is
> truncated from the server name.
> All these connections work locally on the web server (where SQL
> Express is running).
> The network administrator has opened TCP port 1433 on the web server.
> The service has been configured to allow remote connections on TCP/IP
> and Named Pipes.
> >From the remote machine, the same .udl is set up, but with a Data
> Source modified to use an IP address. The Data Source entry then looks
> like 152.21.110.58\SQLEXPRESS. I test the connection and get the error
> "Test connection failed because of an error initializing provider.
> Login timeout expired."
> The network administrator reports attempts to communicate with
> 152.21.110.58 on port 1433. These attempts are being allowed.
> I suspect the data source is the problem. Some documentation indicates
> port 1433 must be explicitly stated when connecting remotely to SQL
> Server 2005 and its variants (including Express). However, I can't
> find any examples of explicit port designation in a .udl.
> The alternative to explicit port connection (according to some
> sources) is to open port 1434 for SQL Browser. That port was opened
> and SQL Browser service started, but the connection failed in the same
> fashion.
> The application that needs to connect is written in C#/.NET.
> Can the .udl be configured to test the remote connection? The
> initiator of the connection is an Oracle box, so SQLCMD is not an
> option for testing remote connections.
> Is there something amiss with the Data Source designation? Everything
> I read points to replacing server specs like '.' and '(local)' with IP
> designations like 152.21.110.68, and keeping the '\SQLEXPRESS' part.
> Is this correct? The SQL Server is a web server in a DMZ, so DNS
> translation is not available.
> How do I monitor SQL Express for login attempts, both local and
> remote?
> Thanks,
> Dan
>
By default SQLExpress does not have network protocols enabled by default see
http://msdn2.microsoft.com/en-us/library/ms190198.aspx ,
http://msdn2.microsoft.com/en-us/library/ms143446.aspx
and
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#disablenetworkprotocols
Using the command line installation option of DISABLENETWORKPROTOCOLS with
values of 0 or 2 will enable Shared Memory and TCP/IP (0 also enables named
pipes)
If this has not be done you can still enable remote connections after you
have installed SQLExpress by using the Surface Area Configuration Tool
(SqlSAC.exe)
to enable remote connections see
http://msdn2.microsoft.com/en-us/library/ms173748.aspx
John

Cannot make remote connection to SQL Server Express

I'm having trouble making a remote connection to a SQL Server Express
database. The service is started, and the connection works locally. I
tested it using a .udl. In this local .udl, I use the SQL Native
Client as the provider, and a Data Source entry of (local)\SQLEXPRESS,
with username/password log on, and initial catalog.
Other Data Source entries that work are .\SQLEXPRESS and LOTSAYOWSAWE
\SQLEXPRESS. The latter entry is a mock-up of the web server's name,
which is 15 characters long, the last 3 being "WEB". The 'B' is
truncated from the server name.
All these connections work locally on the web server (where SQL
Express is running).
The network administrator has opened TCP port 1433 on the web server.
The service has been configured to allow remote connections on TCP/IP
and Named Pipes.

>From the remote machine, the same .udl is set up, but with a Data
Source modified to use an IP address. The Data Source entry then looks
like 152.21.110.58\SQLEXPRESS. I test the connection and get the error
"Test connection failed because of an error initializing provider.
Login timeout expired."
The network administrator reports attempts to communicate with
152.21.110.58 on port 1433. These attempts are being allowed.
I suspect the data source is the problem. Some documentation indicates
port 1433 must be explicitly stated when connecting remotely to SQL
Server 2005 and its variants (including Express). However, I can't
find any examples of explicit port designation in a .udl.
The alternative to explicit port connection (according to some
sources) is to open port 1434 for SQL Browser. That port was opened
and SQL Browser service started, but the connection failed in the same
fashion.
The application that needs to connect is written in C#/.NET.
Can the .udl be configured to test the remote connection? The
initiator of the connection is an Oracle box, so SQLCMD is not an
option for testing remote connections.
Is there something amiss with the Data Source designation? Everything
I read points to replacing server specs like '.' and '(local)' with IP
designations like 152.21.110.68, and keeping the '\SQLEXPRESS' part.
Is this correct? The SQL Server is a web server in a DMZ, so DNS
translation is not available.
How do I monitor SQL Express for login attempts, both local and
remote?
Thanks,
DanHi Dan
"dan@.wagers.net" wrote:

> I'm having trouble making a remote connection to a SQL Server Express
> database. The service is started, and the connection works locally. I
> tested it using a .udl. In this local .udl, I use the SQL Native
> Client as the provider, and a Data Source entry of (local)\SQLEXPRESS,
> with username/password log on, and initial catalog.
> Other Data Source entries that work are .\SQLEXPRESS and LOTSAYOWSAWE
> \SQLEXPRESS. The latter entry is a mock-up of the web server's name,
> which is 15 characters long, the last 3 being "WEB". The 'B' is
> truncated from the server name.
> All these connections work locally on the web server (where SQL
> Express is running).
> The network administrator has opened TCP port 1433 on the web server.
> The service has been configured to allow remote connections on TCP/IP
> and Named Pipes.
>
> Source modified to use an IP address. The Data Source entry then looks
> like 152.21.110.58\SQLEXPRESS. I test the connection and get the error
> "Test connection failed because of an error initializing provider.
> Login timeout expired."
> The network administrator reports attempts to communicate with
> 152.21.110.58 on port 1433. These attempts are being allowed.
> I suspect the data source is the problem. Some documentation indicates
> port 1433 must be explicitly stated when connecting remotely to SQL
> Server 2005 and its variants (including Express). However, I can't
> find any examples of explicit port designation in a .udl.
> The alternative to explicit port connection (according to some
> sources) is to open port 1434 for SQL Browser. That port was opened
> and SQL Browser service started, but the connection failed in the same
> fashion.
> The application that needs to connect is written in C#/.NET.
> Can the .udl be configured to test the remote connection? The
> initiator of the connection is an Oracle box, so SQLCMD is not an
> option for testing remote connections.
> Is there something amiss with the Data Source designation? Everything
> I read points to replacing server specs like '.' and '(local)' with IP
> designations like 152.21.110.68, and keeping the '\SQLEXPRESS' part.
> Is this correct? The SQL Server is a web server in a DMZ, so DNS
> translation is not available.
> How do I monitor SQL Express for login attempts, both local and
> remote?
> Thanks,
> Dan
>
By default SQLExpress does not have network protocols enabled by default see
http://msdn2.microsoft.com/en-us/library/ms190198.aspx ,
http://msdn2.microsoft.com/en-us/library/ms143446.aspx
and
l]
Using the command line installation option of DISABLENETWORKPROTOCOLS with
values of 0 or 2 will enable Shared Memory and TCP/IP (0 also enables named
pipes)
If this has not be done you can still enable remote connections after you
have installed SQLExpress by using the Surface Area Configuration Tool
(SqlSAC.exe)
to enable remote connections see
[url]http://msdn2.microsoft.com/en-us/library/ms173748.aspx" target="_blank">http://msdn2.microsoft.com/en-us/li...y/ms173748.aspx
John