Connection problems: express

Showing posts with label express. Show all posts

Tuesday, March 27, 2012

Considerations... Backing up IIS 6 web files and SQL Server 2005 Express database

Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database?

I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors.

But I have heard a lot of discussion that you can't just simply backup SQL Server data files?

I'm wondering how sound the backup I've created is...

Any suggestions?

Try the link below to dowload the eval version of the full SQL Server 2005 it is good for 180 days so you have to buy the developer edition which is $60 or less later. Install it as a named instance and register the Express so you can use the backup and restore wizard to backup your databases. You don't need any third party tool to backup SQL Server if your databases are less than 1000. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

Sunday, March 25, 2012

Connetion VS2003 C# Web Service to Sql Server 2005 Express

Hi,

I'm reaching the end of my tether here. I'm writing a Web Service application for my MCAD course in VS2003 which will connect to a SQL Server 2005 Express instance to access data. I can connect to the instance through the management suite, and through Visual Studio 2003. For some reason, when I try and manually connect through the web service, I get a page cannot be displayed error. When debugging, it falls over at the connection code. Here's what I have:

</appSettings>

in web.config, and in my application:

// Create a connection to SQL Server Express

connection = new SqlConnection(ConnectionInfo);

// Upload Customer data to DataTable

dataadapter1 = new SqlDataAdapter("select * from Customers", connection);

dataadapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey;

Customer_Data = new DataTable("Customers");

dataadapter1.Fill(Customer_Data);

The line in red is where the application falls over and I get a page cannot be displayed error. Any ideas why my application can't connect yet I can through both the manager and the IDE? I have enabled TCP, named pipes and shared memory. I have TCP dynamic port of 1053, I can connect using SQLCMD -S.\SQLEXPRESS from the command prompt (I get a 1> when I do this). I also have both services up and running. Anyone have any idea?

If the error happens in the red line, then your connection string is wrong. The error occurs in the constructor of the sqlconnection class not during connection which would be the .Open() method.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Ooops. My apologies. I guess working so close to a problem blinds you to its issues. Its working fine now. Well, for now...

Tuesday, March 20, 2012

Connectionstring

Im using sql server express advanced edition and Windows Vista. My Windows
account doesnt have any permissions to the sql server at all, so I need to
use sql server logins in my connectionstring. How can I provide that when
using attachdb?
This is my connectionstring today:
Data Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\site_db.mdf;User
Instance=False;Initial Catalog=site_db;
StigSee http://www.microsoft.com/sql/howtob...stasupport.mspx for
considerations on using SQL 2005 under Vista. Note that SP2 is still in CTP
(beta).
I haven't played around with SQL Express but you ought to be able to add the
desired SQL login credentials in the connection string:
Data Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\site_db.mdf;User
Instance=False;Initial Catalog=site_db;User Id=MyLogin;Password=MyPassword
Hope this helps.
Dan Guzman
SQL Server MVP
"Stig Kulvedrsten" <stig@.spire.as> wrote in message
news:0ABA74C2-E6C4-4E84-B331-3633D0BEC597@.microsoft.com...
> Im using sql server express advanced edition and Windows Vista. My Windows
> account doesnt have any permissions to the sql server at all, so I need to
> use sql server logins in my connectionstring. How can I provide that when
> using attachdb?
> This is my connectionstring today:
> Stig|||I reinstalled and got my sql server express up and running. I then loaded up
VS.NET and tried to run my website.
Right away I got this error:
Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were
in use and max pool size was reached.
What causes this error at first runtime?
Stig
"Stig Kulvedrsten" <stig@.spire.as> wrote in message
news:0ABA74C2-E6C4-4E84-B331-3633D0BEC597@.microsoft.com...
> Im using sql server express advanced edition and Windows Vista. My Windows
> account doesnt have any permissions to the sql server at all, so I need to
> use sql server logins in my connectionstring. How can I provide that when
> using attachdb?
> This is my connectionstring today:
> Data Source=. \SQLEXPRESS;AttachDbFilename=|DataDirect
ory|\site_db.mdf;User
> Instance=False;Initial Catalog=site_db;
> Stig

Monday, March 19, 2012

Connectiong problem Sqlserver 2005 Express

I'm working with an Sqlserver 2005 Express database on my local machine, and using vs.net 2005.I need to enable the sqlserver cache by the way:

aspnet_regsql -E -d Northwind -ed

I encountered the remote connection problem(error:40).The northwind database was restored to the sqlserver 2005 express by the db file downloaded,and I added the ASPNET user.I also enabled the TCP/IP by using SQL Server Configuration Manager.

But all the web application connected to the Northwind database is working through the connection string:

"Data Source=WKS-DEV-04\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"

Any help will be much appreciated, thank you very much for reading my post

Can you telnet to the remote SQL Express service from the client? Using such command from commandline:

telnet xxx.xx.xx.xxx yyyy

Where Xs stand for ip address of the remote server, and Ys stand for TCP port of the service. You can check the TCP port of the SQL service in 'SQL Server Configuration Manager'->SQL Server 2005 Network Configuration

|||Thank you for your reply very much.I have got it done.

Sunday, March 11, 2012

Connection woos

Just the other day I have not been able to connect to SQL Server 2005 Express Edition. NOTHING has changed. All I have noticed is that in the Surface Area Configurator the Local Connections only radio button it selected. So I try selecting the Remote radio button and Apply and Ok and then exit the Surface Area Configurator program and then come back in again and the Local Only is selected.

Is this normal behavior? Is Remote connections allowed or not? How verify?

Thanks.

hi,

do you mean you set the "Allow remote connections" setting and suddenly (the other day) it turned it OFF by magic? and since then (the other day) you are no longer able to enable "remote connections"?

please verify via the SQL Server Configuration Manager you do have a network protocol enabled, restart the SQL Server engine service and, via SAC check remote connections are enabled..

regards

|||

TCP/IP and Shared memory are enabled, and the engine and browser are running.

Remote connections where working, so yes the other day all stopped!

When I start up SAC the Local Only radio button is enabled. So I enabled remote and applied and ok'd.

Come back into SAC again and the Local Only radio button is enabled.

Is this proper behavior, meaning the program never saves the setting?

|||

hi,

Expressman wrote:

Is this proper behavior, meaning the program never saves the setting?

actually not.. did you try restarting the service?

regards

|||

Yes I restart the engine and browser!

I wonder if there could be a virus or hack out now doing it?!

When installing 2005 Express Edition does it associate the
ip address with the database even though ya used a name?

|||

I installed the MS SSEUtil program.

'sseutil - listsrv' command *without quotes' doesn't show any server running. lolol

Yet my program can access the database using the 'computername and instance name'.

Any ideas?

Could a system service not be running?

|||

hi,

Expressman wrote:

I installed the MS SSEUtil program.
'sseutil - listsrv' command *without quotes' doesn't show any server running. lolol
Yet my program can access the database using the 'computername and instance name'.
Any ideas?
Could a system service not be running?

you are probably using a "User Instance"... User Instances do not allow remote connections..

have a look at http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp for further info about this feature..

regards

|||

My new and my old applications used to work remotely without problems.
Both could connect using the server name or ip address.

Now neither one works now, meaning they can't connect.
Message is that the server doesn't exist.

I can only connect using the server name now, NOT the ip address.

I even tried connection with fire wall off. Buggers!

|||

Well I re-installed the database and all works. Thanks to all helping.

Connection woos

Is this normal behavior? Is Remote connections allowed or not? How verify?

Thanks.

hi,

please verify via the SQL Server Configuration Manager you do have a network protocol enabled, restart the SQL Server engine service and, via SAC check remote connections are enabled..

regards

|||

TCP/IP and Shared memory are enabled, and the engine and browser are running.

Remote connections where working, so yes the other day all stopped!

When I start up SAC the Local Only radio button is enabled. So I enabled remote and applied and ok'd.

Come back into SAC again and the Local Only radio button is enabled.

Is this proper behavior, meaning the program never saves the setting?

|||

hi,

Expressman wrote:

Is this proper behavior, meaning the program never saves the setting?

actually not.. did you try restarting the service?

regards

|||

Yes I restart the engine and browser!

I wonder if there could be a virus or hack out now doing it?!

When installing 2005 Express Edition does it associate the
ip address with the database even though ya used a name?

|||

I installed the MS SSEUtil program.

'sseutil - listsrv' command *without quotes' doesn't show any server running. lolol

Yet my program can access the database using the 'computername and instance name'.

Any ideas?

Could a system service not be running?

|||

hi,

Expressman wrote:

I installed the MS SSEUtil program.
'sseutil - listsrv' command *without quotes' doesn't show any server running. lolol
Yet my program can access the database using the 'computername and instance name'.
Any ideas?
Could a system service not be running?

you are probably using a "User Instance"... User Instances do not allow remote connections..

have a look at http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp for further info about this feature..

regards

|||

My new and my old applications used to work remotely without problems.
Both could connect using the server name or ip address.

Now neither one works now, meaning they can't connect.
Message is that the server doesn't exist.

I can only connect using the server name now, NOT the ip address.

I even tried connection with fire wall off. Buggers!

|||

Well I re-installed the database and all works. Thanks to all helping.

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properl

I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

I have two SQL Server instances installed.

1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection 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: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

Hello,

Attach the files to your SQL server instance using for example the SQL management Studio and change your connectionstring to connect into this database.

|||

Well the thing is that I dont have a database created yet. I am trying to create a new database. If i create a database in SQL 2005 then my connectionstring will point to the database on sql server. I need the database to reside in the VS project like a stand alone .mdf file so that I can deply the database with my project.

Does that make sense? Thanks for your help.

|||

Hi,

You may open your Machine.Config file (the file is in %SystemRoot%\Microsoft.NET\Framework\ver. number\CONFIG ). Try to find the ConnectionString node, modify the setting and make it look like

Thanks.

Connection to SQL Server 2005 Express?

Hi all, I've been reading other posts for awhile now and I can't figure out what I'm doing wrong.
I'm a Web Developer and not a DBA. I have SQL Server 2005 Express installed on my machine and it's working just fine. I also have the Management Studio Express CTP installed and working.
I'm trying to connect to a database using a username and password (from ColdFusion Server which is working fine for external Oracle and Access databases, so that's not an issue), so I set up the server to allow mixed mode authentication. I can log in to the Management Studio using Windows authentication OR the username and password without any issues.
I have TCP/IP enabled in the Configuration Manager and 127.0.0.1 enabled.
This seems like a ridiculous question, but I need to know the servername, port, username and password to connect to the database. I'm trying all combinations of things but nothing's working.
If I put in 127.0.0.1 as the server, 1433 as the port, and use the username and password that worked in Management Studio I get this:
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
If I use MACHINENAME\SQLEXPRESS I get this:
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
That makes me think I'm on the right track with 127.0.0.1 but what should I do at this point?
Edit: I have also tried to connect to my local SQL Server Express via the Migration Assistant. If I use 127.0.0.1 I get this:
Connection to SQL Server failed.
Please make sure that TCP/IP protocl is enabled on your target server and its TCP port is not blocked by a firewall.
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
I'm really at a loss and I wish I knew more about this. Thanks for any advice!Try one of two things:

(1) Instead of port 1433 specify the port obtained from SQL Server Configuration Manager: SQL Server 2005 Network Configuration -> Protocols fro SQLEXPRESS -> TCP/IP -> Properties -> IP Addresses -> IP All -> TCP Dynamic Ports.

(2) Start the SQL Browser service, and specify the server as MACHINENAME\SQLEXPRESS.|||Were you able to find a solution to this issue? I'm having the same problem.

Rob|||No, unfortunately I still can't get it to work. I've moved to another testing environment with SQL Server 2000 and that's working fine. If you find a way to get this to work I'm very interested!|||Peter is the man! First off, in SQL Server Config Manager, under Network Config. Found that TCP/IP was Disabled. Once enabled, I had to restart my instance, then got the TCP Dynamic Port - plugged that into Cold Fusion Administrator and BOOYAH!

No need to muck with the server as MACHINENAME\SQLEXPRESS, I left it blank (default to 127.0.0.1).

Thanks!
|||Is the port being blocked by Firewall. If yes, then the port needs to be opened explicitly.|||I am able to connect to my sqlexpress instance from a machine with sqlserver 2003 but not from a similar machine with sqlserver 2000. I have done the stuff listed above and still cant get an udl to work. There are no intervenig firewalls as the non-connecting machines are on the same subnet. Anybody got another guess.

|||

I had the same problem, and yes it is now working.

If you are using SQL EXPRESS 2005 here it is:

Open SQL Serve Configuration Manager

under SQL Server 2005 Network configuration, click on Protocole for SQLEXPRESS, then TCP/IP, right-click on it and go in properties. Select the IP adresses Tab, at the bottom you gonna see IPALL - check what is your TCP DYNAMIC PORTS, For me the port number is 2711.

In CF Administrator I have put server 127.0.0.1 and the port 2711 and everything work fine.

Hope this help

|||

The solutions here worked for me.

Just be sure you have looked at webacadie's post above. Also be sure you have the sqlserver browser service and the database service both running... My problem was I didn't have the browser service running.

|||I still can't get this thing to connect.
I went through all the suggestions in this forum and still I cannot connect with SQLEXRESS, so far this db has been the most difficult to connect to that I have encountered.

|||

Hi les_ihs,

I'm sorry that you're having so much problems getting connections to work. Perhaps this information contained within this blog posting may help: http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx.

Thanks,

Il-Sung.

|||I had the exact same problem. The confusing part for me was the fact that it says "SQLServer 2000 JDBC driver" while I was using SQL Server 2005.

In the end all I had to do was Start the SQL Server Browser service. (which required me to first change its start mode...). A few hours of my time wasted trying to solve this problem...could have all been avoided if the SQL Server Migration Assistant I was trying to connect with had decent documentation.

Connection to SQL Server 2005 Express?

(2) Start the SQL Browser service, and specify the server as MACHINENAME\SQLEXPRESS.|||Were you able to find a solution to this issue? I'm having the same problem.

No need to muck with the server as MACHINENAME\SQLEXPRESS, I left it blank (default to 127.0.0.1).

|||

I had the same problem, and yes it is now working.

If you are using SQL EXPRESS 2005 here it is:

Open SQL Serve Configuration Manager

In CF Administrator I have put server 127.0.0.1 and the port 2711 and everything work fine.

Hope this help

|||

The solutions here worked for me.

|||

Hi les_ihs,

Thanks,

Il-Sung.

|||I had the exact same problem. The confusing part for me was the fact that it says "SQLServer 2000 JDBC driver" while I was using SQL Server 2005.

Connection to SQL Server 2005 Express?

(2) Start the SQL Browser service, and specify the server as MACHINENAME\SQLEXPRESS.|||Were you able to find a solution to this issue? I'm having the same problem.

No need to muck with the server as MACHINENAME\SQLEXPRESS, I left it blank (default to 127.0.0.1).

|||

I had the same problem, and yes it is now working.

If you are using SQL EXPRESS 2005 here it is:

Open SQL Serve Configuration Manager

In CF Administrator I have put server 127.0.0.1 and the port 2711 and everything work fine.

Hope this help

|||

The solutions here worked for me.

|||

Hi les_ihs,

Thanks,

Il-Sung.

|||I had the exact same problem. The confusing part for me was the fact that it says "SQLServer 2000 JDBC driver" while I was using SQL Server 2005.

Connection to SQL Server 2005 Express?

The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
If I use MACHINENAME\SQLEXPRESS I get this:
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.

The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
That makes me think I'm on the right track with 127.0.0.1 but what should I do at this point?
Edit: I have also tried to connect to my local SQL Server Express via the Migration Assistant. If I use 127.0.0.1 I get this:
Connection to SQL Server failed.

Please make sure that TCP/IP protocl is enabled on your target server and its TCP port is not blocked by a firewall.

[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
I'm really at a loss and I wish I knew more about this. Thanks for any advice!Try one of two things:

(2) Start the SQL Browser service, and specify the server as MACHINENAME\SQLEXPRESS.|||Were you able to find a solution to this issue? I'm having the same problem.

No need to muck with the server as MACHINENAME\SQLEXPRESS, I left it blank (default to 127.0.0.1).

Thanks!|||Is the port being blocked by Firewall. If yes, then the port needs to be opened explicitly.|||I am able to connect to my sqlexpress instance from a machine with sqlserver 2003 but not from a similar machine with sqlserver 2000. I have done the stuff listed above and still cant get an udl to work. There are no intervenig firewalls as the non-connecting machines are on the same subnet. Anybody got another guess.|||

I had the same problem, and yes it is now working.

If you are using SQL EXPRESS 2005 here it is:

Open SQL Serve Configuration Manager

In CF Administrator I have put server 127.0.0.1 and the port 2711 and everything work fine.

Hope this help

|||

The solutions here worked for me.

Hi les_ihs,

Thanks,

Il-Sung.

|||I had the exact same problem. The confusing part for me was the fact that it says "SQLServer 2000 JDBC driver" while I was using SQL Server 2005.

Connection to SQL Server 2005 Express?

Please make sure that TCP/IP protocl is enabled on your target server and its TCP port is not blocked by a firewall.

[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
I'm really at a loss and I wish I knew more about this. Thanks for any advice!Try one of two things:

(2) Start the SQL Browser service, and specify the server as MACHINENAME\SQLEXPRESS.|||Were you able to find a solution to this issue? I'm having the same problem.

No need to muck with the server as MACHINENAME\SQLEXPRESS, I left it blank (default to 127.0.0.1).

Thanks!|||Is the port being blocked by Firewall. If yes, then the port needs to be opened explicitly.|||I am able to connect to my sqlexpress instance from a machine with sqlserver 2003 but not from a similar machine with sqlserver 2000. I have done the stuff listed above and still cant get an udl to work. There are no intervenig firewalls as the non-connecting machines are on the same subnet. Anybody got another guess.|||

I had the same problem, and yes it is now working.

If you are using SQL EXPRESS 2005 here it is:

Open SQL Serve Configuration Manager

In CF Administrator I have put server 127.0.0.1 and the port 2711 and everything work fine.

Hope this help

|||

The solutions here worked for me.

Hi les_ihs,

Thanks,

Il-Sung.

|||I had the exact same problem. The confusing part for me was the fact that it says "SQLServer 2000 JDBC driver" while I was using SQL Server 2005.

Connection to SQL Server 2005 Express?

(2) Start the SQL Browser service, and specify the server as MACHINENAME\SQLEXPRESS.|||Were you able to find a solution to this issue? I'm having the same problem.

No need to muck with the server as MACHINENAME\SQLEXPRESS, I left it blank (default to 127.0.0.1).

|||

I had the same problem, and yes it is now working.

If you are using SQL EXPRESS 2005 here it is:

Open SQL Serve Configuration Manager

In CF Administrator I have put server 127.0.0.1 and the port 2711 and everything work fine.

Hope this help

|||

The solutions here worked for me.

|||

Hi les_ihs,

Thanks,

Il-Sung.

|||I had the exact same problem. The confusing part for me was the fact that it says "SQLServer 2000 JDBC driver" while I was using SQL Server 2005.

Thursday, March 8, 2012

Connection to SQL Server

I have installed MS SQL Server 2005 Express Edition with Advanced Services and MS SQL Server 2005 Express Edition Toolkit SP1. Everything istalled without any errors noted. When starting SQL Server Management Studio Express, accepting the server name MAYNARD-MAIN with authentication as windows authentication. I get the following message:

Cannot connect to MAYNARD-MAIN

Additional information:

An error has occurred while establishing a connection to the server. Wehn connecting to SQL Server 2005, this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections.(provider:Shared Memory Provider, error:36 - The Shared Memory dll used to connect to SQL Server was not found)(Microsoft SQL Server, Error: 126)

I haven found any info on either of these error in the forum.

The Configuration Manager shows the following:

SQL Server FullTextSearch(SQLEXPRESS) - Running

SQL Server(SQLEXPRESS) - Running

SQL Server Reporting Services(SQLEXPRESS) - Running

SQL Server Browser - Running

Protocols for SQLEXPRESS

Shared Memory - Enabled

All others disabled

Client Protocols

Shared Memory - Order 1 - Enabled

TCP/IP - Order 2 - Enabled

Named Pipes - Order 3 - Enabled

VIA - Disabled.

What do I need to be trying to find to corect this.

Either connect to the default server instance..."SQLEXPRESS" or Make sure that the "MAYNARD_MAN" instance is running before trying to connect to it

|||Well that was easy enough - I really feel dumb now. Add instance to machine name and it works a lot better. Sorry I took up your time being not to bright.

Connection to SQL Express ONLY with integrated security but WITHOUT domain

Does anybody know if it is possible to establish a connection to an sql express instance only with integrated security when this express instance is running on XP which is NOT part of a domain?

Thanks,

Klaus

If the SQL Express machine isn't connected to a domain then I don't believe Kerberos authentication will work but NTLM authentication should work. If the client application is running on the same machine as SQL Express then you should be able to straightforwardly give a user account local to the machine permission to connect to SQL Express (machine administrators will have it by default). If you're connecting from off-box then I think you'll need to setup the same account (same user name & password) on the remote machine.

Hope that helps.

Vaughn

Wednesday, March 7, 2012

Connection to database problems

Hi,

I am trying to create a program in VS2005 - C# that uses SQL Express as it's database.

Here is the connection string:

@."Data Source=(local)\SQLEXPRESS;Initial Catalog=MyBookings;Integrated Security=True";

Here is the result from the log file:

2007-03-21 10:49:44.36 Logon Login failed for user 'AQUARIUS\Administrator'. [CLIENT: <local machine>]

I could play with this manually and probably get it working BUT this is a solution that is to be installed on computers by users who will have no knowledge of SQL Express.

The database needs to be accessible by the program without user intervention.

How do I go about this or fix the above problem?

Thanks.

hi,

the connection string is correct, so you should check the actual connecting Windows login has been granted connection permissions to the Express instance...

loosely speaking, SQL Server manages security using a so called "2 phases" authentication policy..

the first one is dedicated to connection action, where the provided credentials are validated against the granted logins "list"... granted logins are "principals", both standard SQL Server logins (requiring explicit user name and password credentials) and Windows logins (where only the relative sid is provided as credential as already authenticated by the domain controller/computer they are member of) that have been granted permissions to log in the specified SQL Server instance... for standard logins, like "sa", you have to manage each specific login, where, for Windows logins, you can both manage them at single login granularity as long as at Group granularity..
So, for Windows logins, you can grant connection permissions at "AQUARIUS\AccountName" or, for group management, at "AQUARIUS\GroupName" the specified account is member of...
by default, SQL Server 2005 grants login permissions for integrated security validated connections at LocalAdministrators (BUILTIN\Administrators) and LocalUsers (BUILTIN\Users) members, but you have to manage as required further logins or groups, as long as to a special standard SQL Server login, "sa", if non integrated security (or "mixed security") connections have been enabled as well..
If the provided credentials can not be validated, the connection is refused and the exception you provided is raised...

when the first security phase has been positively validated, SQL Server proceeds in the second one, regarding database access to the specified one... every kind of server level principal but members of the sysadmins server role requires explicit database access permission via a mapping to a database level principal, usually known as a "database user"..

when resolved, the particular login, mapped to a specified database user, can "access" the specified database... then additional security factors comes to play, to permit security constraints at database object level, like table, view, stored procedure, ...., and with more granularity as well, at column level for tables and views database objects, in order to grant permissions according to the required security path, so that only particular database principals can execute the particular stored procedure(s) or select from the particular table(s) or update the particular table(s)...

regards

|||

if your DB is in Local computer use this

SqlConnection myConnection = new SqlConnection("server=(local)\\SQLEXPRESS;Integrated Security=SSPI;database=yourDBname");

if your DB is in App_Data folder use this

SqlConnection myConnection = new SqlConnection("Data Source=.\\SQLEXPRESS; AttachDbFilename=|DataDirectory|\\yourDBname.mdf;Integrated Security=True;User Instance=True");

Thanks
www.sqltree.com

Connection Timeout - Need Help

Hello,

My app uses sql express on the client work station and has been installed by our application installer as a part of the prerequisites. The sql express install in standard and we've not changed any settings or brought in any tweaks.

All is well in our dev center. However, some of our clients are experiencing a problem when the app starts up. After the PC has been restarted, running the application causes it to crash with a connection time out. However, running it a second time will work with absolutely no issues.

Overall - after a restart, the first sql express connection will time out. I had a post on this earlier here -> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=93279&SiteID=1

MS Lee had suggested -- > "As for the first connection failing and subsequent connections succeeding, one potential cause is the cached last connect information. If you're connecting to a named instance and not specifying a port, then the client will determine the port by querying SQL Browser on the server"

I'm not using a remote sql express. It is running on the local machine. My connection string looks like -->

"Integrated Security=True;User Instance=True;Data Source=.\SQLExpress;AttachDBFilename=<%USERS_LOCAL_FOLDER%>;"

What may be the problem ? Any help/advise will be great. I'll need to sort this out ASAP.

Thanks,

Avinash

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)

at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()

at System.Data.SqlClient.TdsParserStateObject.ReadByte()

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at ***.**.Base.DataServices.DataFactory.SQLProvider.GetConnection(String connectionString) in c:\DevNet\SCAH\***.**.Base.DataServices\DataFactory\SQLProvider.cs:line 30

at ***.**.Base.DataServices.DalContext.OpenConnection() in c:\DevNet\SCAH\***.**.Base.DataServices\DALContext.cs:line 166

at ***.**.Base.DataServices.DataManager.GetTable(String sql, String tableName, DataSet dataSet) in c:\DevNet\SCAH\***.**.Base.DataServices\DataManager.cs:line 93

Hi Avinash,

I'd recommend setting the Connect Timeout or Connection Timeout values in the connection string to a higher value. I believe the default is 60 seconds.

The most likely cause of the problem you area seeing is a result of how User Instances work. User Instances launch a new process of SQL Server that runs in the context of the application user. In order to do this, we need to setup some directories in the user's profile and copy some files into that directory. This takes some time to do, and it can cause a timeout to occur the first time the User Instance is created.

That work only needs to be done once, so subsequent connection succed as you've found.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||

Hello Mike W,

Many thanks for your response. I'll go ahead and try that out.

Question - will the folders/files need to be set up each time the sql process is started ? Won't it use ones created from a previous run ?

Also - some of the clients experiencing this problem are running very modern laptops with generous amounts of RAM and Hard Disk space being available. They were found to be rather light and no heavy apps/processes were running when this occurred. In that light - I suppose 60 Seconds is reasonable time to get started. Thoughts ?

But, I'll go ahead and try it - we'll see how it goes with our next build.

Thanks again.

Regards,

Avinash

|||

Hi Avinash,

The files and folders only need to be created once per user. The second time you try to connect, the folders and files are there and those are used. This is why you're seeing the behavior of a timeout the first time, but success the second (and subsequent) times.

It doesn't really have much to do with how modern your computer is, it has to do with HD access time and how long it takes to copy a file. Simply put, the default timeout is too short to accomplish both the file copy and the connection. I believe the default timeout is 60 seconds, so I'd go with something longer. A little trial and error should get you the right time.

Regards,

Mike

Mark the best posts as Answers!

|||

One other possibility I can think of is: You have some network issue with slow DNS.

Every time you make a connection, we need to do a couple of DNS resolve (in some cases both directions, IP <--> DNS). The connection may timeout if you DNS is slow. The second, windows will pick up the DNS caches and you can get connected much faster. If you know a machine that can expecience this first-time-timeout issue for sure, you can try to ping the name of the server machine before you connect the server. If the issue is gone in this case, you know the reason is slow DNS.

Another way to verify, run "ipconfig /flushdns" from command window and try connect to the server afterwards. See whether you see the timeout issue this time.

|||

This may be a dumb question. But I'll go ahead and ask anyway.

Is this valid in the case of SQL Express and particularly when both the client application(C# Smart Client) accessing the express database and SQL Express itself are running on the same machine ? Cause this is the mode in which we are seeing this issue.

Will a DNS resolve be needed when we use ".\SQLExpress" in the connect string ?

Thanks,

Avinash

|||

I would guess that DNS has nothing to do with accessing a local instance of SQL Express. As I said in an earlier post, the most likely issue here is the delay caused by the file copy that is required the first time (and only the first time) you create a User Instance for a given user.

Has adjusting the timeout eliminated the problem?

Regards,

Mike

|||

Hello Mike,

The last build has just been shipped out. I've set the time out to 120 seconds. Should hear from our clients in a few days.

Will let you know how it goes.

Thanks for all the help,

Regards,

Avinash

|||

Hello Mike,

Just to let you know that increasing the connection time out has indeed solved the problem.