Connecting to SQL Server 2005 - Not Allow Remote Connections

I use ASP.NET 2.0 and VS.NET 2005.

I have the following expression in my code:

string connectionString = "server=\'localhost\'; user id=\'sa\'; password=\'\'; Database=\'Northwind\'";

When I executed the code I have the following error message:

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

The Microsoft Access was successfully connected in the Server Explorer window.

How do I solve this problem?

Thanks,

Andy.

Try this thread for the two links you need to get it going. Hope this helps.

http://forums.asp.net/thread/1381596.aspx

|||

From your link, I found the following link http://support.microsoft.com/default.aspx?scid=kb%3bEN-US%3b914277. I have followed all the instructions from that page, but I still receive the same error message.

I am not sure which version of SQL I am using. From the "start" -> "All Programs", I can only see "Microsoft SQL Server". It was installed, when I installed Microsoft Visual Studio 2005 Professional.

Andy.

|||If you have the professional you should have the Developer edition CD separately, copy the content to your hard drive and install it as a named instance and then use the instructions for the developer edition. Hope this helps.

Connecting to SQL Server 2000 Named Instances

I have the following problem;

I fully understand that to connect to a named instance of SQL Server you need to use the ServerName\SQLInstanceName. The problem I have is that I have a SQL Server in a different zone. I can connect to the Default instance by IP Address or the ServerName.zone.domain.org. (e.g. MySQLServer.zone1.mydomain.org).

However, the same thing does not work for the Named Instance. It seems to be named instance or nothing.

How can I connect to this named instance across network zones?:Suse the instance name with the ip address, like 1.2.3.4\myInstance and you ought to be fine.

-PatP|||That did it. Thanks!!! :beer:

Connecting to SQL from JBuilder

Hi everyone,

I am trying to connect to a SQL Server Database from an aplication in java, but I received the following error: No suitable driver

I am using JDBC and a ODBC connection call TP2. My string connection look like this

Connection con = DriverManager.getConnection ("jdbc:odbc:TP2", "sa", "sql");

What I do not have clear enought is where should I copy my jar files ??

I really appreciate your help !!!!!!!!!!!!!!
BRthis link may help u. http://forum.java.sun.com/thread.jspa?threadID=720810
and this, http://www.devdaily.com/java/edu/pj/pj010024/ if it is a connection error.
I ve no idea about the jar file u mentioned,better u post ur question in java forum.|||Download the MS JDBC driver or the jTDS JDBC driver

Connecting to SQL from IIS

Hi All,
I'm attempting to connect my .asp page to an SQL 2005 server and I'm
receiving the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user ''. The
user is not associated with a trusted SQL Server connection.
The SQL server is set for mixed authentication and my connection string
points to an ODBC connection on my web server (and it showed that it was
successfully able to connect after the DSN was created). Are there other
steps on my SQL server that need to be performed to allow for authentication
from IIS? I will also post my connection string below from my ASP page:
<%
Const adOpenStatic = 3
Const adLockReadOnly = 1
'Open Connection to Database
set Conn = Server.CreateObject("ADODB.Connection")
qryDSN = "DSN=BT"
Conn.open qryDSN
set rs = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT CO_COMPANY_NAME FROM COMPANIES"
rs.open sql, conn, adOpenStatic, adLockReadOnly
rs.movefirst
do while not rs.EOF
response.Write(rs.fields(0))
loop
%>
Any help is appreciated!!
Joe Corey
MCSE, MCSA, Security+
jcorey@.andrew.cmu.eduIs the DSN using a SQL Authen. or WIndows Authenticatio. I guess it is
using Windows Authentication and the SQL Server is not on the same box
like the IIS Server. If so, and my guess was right, you will have to
enabled the IIS to delegate authentication. More about this can be
found on the MSDN knowledge base article. Simply search for "Delegation
AND SQL SERVER". After setting up a security principal name SPN you
will be able to authenticate the users.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Connecting to SQL Failed

I am trying to connect to SQL following the directions in Learn ASP.NET in 24 hours I am on page 332-334 where i try to create a Data base for the first time.

I am using a localhost configuration.

I get the following message

"Unable to connect to database server.

SQL does not exist or access denied.

ConnectionOpen(Connect())"

However I see that SQL server(SQLEXPRESS) and SQL server browser are both running and their start state is automatic

Hi,

You can reach SQLEXPRESS instance as ComputerName\SQLExpress

Connecting to SQL Express Database in C# Application

I'm trying to connect to a sql express database on my local machine, using the following connection string:

SqlConnection thisConnection = new SqlConnection(@."Data Source=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf");

However, I keep getting an error message saying "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

Why would my application be trying to connect remotely when the database is on my local machine? And how can I fix this problem?

Thanks

There 'should' be three major components to a connection string: source (or server), catalog (or database), and security information. You have only a source listed.

Check out legitimate SQL Server connection strings at: http://connectionstrings.com/?carrier=sqlserver2005

|||Thanks for the help!

Connecting to SQL Express 2005 via Web.Config file in Visual Studio 2003

Hi

I am following a project to build a small E-Commerce site from a book I have purchased and I have having problems connecting to the SQL Database with the code supplied.

The book is Apress Beginning ASP.NET 1.1 E-Commerce

http://www.amazon.co.uk/Beginning-ASP-Net-1-1-E-Commerce-Professional/dp/1590592549/ref=sr_11_1/202-7684451-7995058?ie=UTF8&qid=1193780707&sr=11-1

The code added to the Web.Config file is :

<configuration>

<appSettings>

<addkey="ConnectionString"value="Server=(local)\NetSDK;Integrated Security=True;Initial Catalog=JokePoint"/>

</appSettings>

The connection details are in a class file called Catalog.vb and is as follows

Imports System.Data.SqlClient

PublicClass Catalog

PublicSharedFunction GetDepartments()As SqlDataReader

'Create the connection object

Dim connectionAsNew SqlConnection(connectionString)

'Create and initialize the command object

Dim commandAsNew SqlCommand("GetDepartments", connection)

command.CommandType = CommandType.StoredProcedure

'Open the connection

connection.Open()

'Return a SqlDataReader to the calling function

Return command.ExecuteReader(CommandBehavior.CloseConnection)

EndFunction

PrivateSharedReadOnlyProperty connectionString()AsString

Get

Return ConfigurationSettings.AppSettings("ConnectionString")

EndGet

EndProperty

EndClass

The error is....

Cannot open database "JokePoint" requested by the login. The login failed. Login failed for user 'MachineName\ASPNET'.

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 database "JokePoint" requested by the login. The login failed. Login failed for user 'MachineName\ASPNET'.

Source Error:

Line 15: 'Open the connectionLine 16:Line 17: connection.Open()Line 18: Line 19: 'Return a SqlDataReader to the calling function

Source File:C:\MyCommerceSite\JokePoint\BusinessObjects\Catalog.vb Line:17

Stack Trace:

[SqlException: Cannot open database "JokePoint" requested by the login. The login failed.Login failed for user 'MachineName\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() JokePoint.Catalog.GetDepartments() in C:\MyCommerceSite\JokePoint\BusinessObjects\Catalog.vb:17 JokePoint.DepartmentsList.Page_Load(Object sender, EventArgs e) in C:\MyCommerceSite\JokePoint\UserControls\DepartmentsList.ascx.vb:44 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Control.LoadRecursive() System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()

The connection tests ok in Visual Studio 2003. All permissions are set using SQL Management Studio Express 2005.

The book is using SQL 2000, I have been trying different connection syntax's in the Web.Config file all day and now I have a big headache. I know its something simple, can anyone please advise ?

Thanks in advance.

Mark

cracken:

<addkey="ConnectionString"value="Server=(local)\NetSDK;Integrated Security=True;Initial Catalog=JokePoint"/>

First, you are trying to connect using windows authentication and not with a SQL Server user credentials. I think the Integrated Security part is causing you the error. Have you tried to connect using SQL Server user credentials ( if any ), or you are using the windows authentication only.

Hope this will help.

|||

Try this...

web.config file:

<connectionStrings>

<addname="ConnectionString";Data Source=(local)\NetSDK;Integrated Security=True;Initial Catalog=JokePoint";

providerName="System.Data.SqlClient" />

</connectionStrings>

after that in the vb page:

Dim sepAsNew SqlConnection("connectionstring")

sep.Open()

Dim commandAsNew SqlCommand("GetDepartments", sep)

command.CommandType = CommandType.StoredProcedure

|||

Hi

Thanks for answering so quick, I am using Windows Integrated Secuity at the moment.

I have tried the suggestion above, but they were not sucessful. What I have done is changed the provier to OleDb and added a bit to the connection string.

I do not seen to get any code errors now, just login errors.

The VB file now looks like:

Imports System.Data

Imports System.Data.OleDb.OleDbConnection

Public Class Catalog

Public Shared Function GetDepartments() As OleDb.OleDbDataReader

'Create the connection object

Dim connection As New OleDb.OleDbConnection(connectionString)

'Create and initialize the command object

Dim command As New OleDb.OleDbCommand("GetDepartments", connection)

command.CommandType = CommandType.StoredProcedure

'Open the connection

connection.Open()

'Return a OleDbDataReader to the calling function

Return command.ExecuteReader(CommandBehavior.CloseConnection)

End Function

Private Shared ReadOnly Property connectionString() As String

Get

Return ConfigurationSettings.AppSettings("ConnectionString")

End Get

End Property

End Class

And the Web.Config connection string :

<configuration>

<appSettings>

<addkey="connectionString"value="Server=(local)\NetSDK; Provider=SQLOLEDB; Integrated Security=SSPI; Initial Catalog=JokePoint"/>

</appSettings>

And the error message :

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

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.OleDb.OleDbException: Cannot open database "JokePoint" requested by the login. The login failed.

Source Error:

Line 16: 'Open the connectionLine 17:Line 18: connection.Open()Line 19: Line 20: 'Return a OleDbDataReader to the calling function

All access accounts and read / write permission are correct.

Any more advise would be much app.

Regards

Mark

Connecting to SQL database

Hi all,

Please help me with the following problem because I am running around in circles.

I have this problem getting a forum online.

Since it is a database related problem which I have been trying to solve at the ClubSite forum I am taking the liberty to come to this forum with this problem.

This is the story:

I have this separate SQL databasedotForum2004.mdf in App_data.

After trial and error got it working locally.

But when I put it online at my webserver (On a Stand-alone PC at my jobsite) I get an application error.(From the Club Site application)

This is the connection key which works fine locally. Is there something obvious you can point me at?

<addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Trusted_Connection=Yes;" />

Thanks in advance,

Lex

Hi Lexy,

From you connection string I notice you use Windows Authentication (Trusted_Connection=Yes) to connect to database. And as I know when the project is deployed to website, it will run under an account (maybe called 'NT AUTHROTY\NETWORK SERVICE' or something like this, sorry I'm not sure) rather then under current Windows Login account. So you can check the connection error message, is it something like this?

Login failed for 'XXXXXXX'

Where 'XXXXXX' stands for an account. Then check the logins in you SQL Server instace (it is '.\SQLExpress' in your application). Add the account to SQL logins and give it proper permission to access database objects; or you can add it to 'sysadmin' role so that it can access all database objects.

|||

Hi lori_Jay,

Thanks for your response,

I put <customErrors mode="Off"/> in my web.config and now at least I see the error. (see below on this post)

But I'm afraid you lost me with the part:

Then check the logins in you SQL Server instace (it is '.\SQLExpress' in your application). Add the account to SQL logins and give it proper permission to access database objects; or you can add it to 'sysadmin' role so that it can access all database objects.

Got no idea where in the application i can check the logins like you suggest. Let alone the rest of your advice.

Lex

CREATE DATABASE permission denied in database 'master'.

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.OleDb.OleDbException: CREATE DATABASE permission denied in database 'master'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OleDbException (0x80004005): CREATE DATABASE permission denied in database 'master'.] System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1054817 System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.OleDb.OleDbConnection.Open() +37 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86 dotForumClientTopic.viewAllRecords.bindData() +169 dotForumClientTopic.viewAllRecords.Page_Load(Object sender, EventArgs e) +32 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

---

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

|||

Sorry for misunderstanding^_^

Anyways we got closer to the cause. From the error message we can see the problem is the Login used in applcation connection did not have sufficient permission to CREATE DATABASE; whileCREATE DATABASE permission defaults to members of thesysadmin anddbcreator fixed server roles. So let's try to add the 'NT AUTHORITY\NETWORK SERVICE' (or 'ASPNET', something like these which used by IIS when connecting to SQL with Windows Authentication) to sysadmin role. You can do this simply in Management Studio->MyInstance->Security->Logins; or if you do not have Management Studio, just try to run this T-SQL command:

EXEC sp_addsrvrolemember 'NT AUTHORITY\NETWORK SERVICE' ,'sysadmin'

|||

Hi,

You just made me a happy man!

If you new how long this has been bugging me.

Adding Sysadmin role to <Servername>\ASPNET did the trick.

Now at last I can get on with all the other Forum problems.

Thanks again,

Lex

|||

Hi again,

Now the Forum is finally online I am trying to evaluate a number of things so I get the optimal result in learning from the problem you solved.

Is there a preferred way for Authentication.to SQLserver /express. Would it be better to go for SQL Server Authentication?

--------

I also had to addDatabase=dbname

Old:<addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Trusted_Connection=Yes;" />

New: <

addkey="forumDSN"value="Provider=SQLOLEDB; Server=.\SQLExpress;AttachDbFilename=|DataDirectory|dotForum2004.mdf;Database=dbname;Trusted_Connection=Yes;Language=Dutch" />

Somehow during my various attempts (of trial and error) I must have created this database.

I tried to change that part of the key in Database=Forum

Locally a database with the name Forum appears in Management Studio, but I get an error when I try it on the webserver. I thought again this could be solved with security, so I checked.

All roles (including the ones you advised have dbcreator rights) Was this Database dbname then created manuelly at some time, or am i overlooking something else?

Thanks in advance,

Lex

Connecting to Reporting Services in Management Studio

I get the following error in connectig to RS in Management Studio - what do I
need to do to fix this - thanks.
Paddy
--
The report server has encountered a configuration error. See the report
server log files for more information. (rsServerConfigurationError)
Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\RSReportServer.config' is denied.Go to the NTFS Permissions of C:\Program Files\Microsoft SQL
Server\MSSQL.3\Reporting Services\ReportServer\RSReportServer.config'
and make sure that ASPNET computer account has read access to this file.
or if the ReportServer directory has the correct permissions then just check
the inherit permission from parent checkbox.
"Paddy" wrote:
> I get the following error in connectig to RS in Management Studio - what do I
> need to do to fix this - thanks.
> Paddy
> --
> The report server has encountered a configuration error. See the report
> server log files for more information. (rsServerConfigurationError)
> Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\RSReportServer.config' is denied.

Connecting to reporting services

I am trying to connect to my reporting services. I keep getting the following error. I am in the sysadmin role What else do I need access to in order to connect?
TIA,
Joe
TITLE: Connect to Server

Cannot connect to ENSQLD1\ENSQLD1_2005.

ADDITIONAL INFORMATION:

The request failed with HTTP status 401: Access Denied. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

OK

Are you a local system admin to the Report Server? If not then a local admin will have to grant you rights to the Report server. The Database roles have no meaning to the Report Server service.

-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.sqlsql

Connecting to reporting services

I am trying to connect to my reporting services. I keep getting the following error. I am in the sysadmin role What else do I need access to in order to connect?
TIA,
Joe
TITLE: Connect to Server

Cannot connect to ENSQLD1\ENSQLD1_2005.

ADDITIONAL INFORMATION:

The request failed with HTTP status 401: Access Denied. (Microsoft.SqlServer.Management.UI.RSClient)

BUTTONS:

OKAre you a local system admin to the Report Server? If not then a local admin will have to grant you rights to the Report server. The Database roles have no meaning to the Report Server service.

-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.

Connecting to Remote Database using EM

Hi to all:

I always had a problem connecting to SQL Server from home using EM.

I do add a new server registration, but i always get the following exception:

A Connection could not be established to IP ADDRESSReason: SQL Server does not exist or access denied.ConnectionOpen(Connect())

At the same time, I do use a utility, found athttp://sql.bhaidar.net to connect to the same database and works perfect.

Is there any configuration I should do to my pc, don't know maybe some port configuration or so, to allow EM to connect to those remote databases?

Thanks in advance.

Regards

Can you 'ping' the IP address successful? If yes, can you 'telnet' the IP with the port used by SQL server service?(use Network Utility to configure it) How about force to connect with TCP/IP? That means when you register the server in EM, using 'tcp:xxx.xx.xx.xx,yyyy' as server identifier (where x stands for IP address, and y for port number)|||

Well, I am unable to ping it, although it is a working server. Is it something related to Win XP FireWall? Should I allow a port or so?

Thanks

|||

Maybe. As I know the Internet Connection Firewall (ICF) on WinXp does not allow resposing to ICMP packages (used by ping) from unknown IP. You can configrue this in ICF: go to 'Advance' pannel->'Local Area Connection'->Find ICMP pannel->enable 'Allow Response...'(something like this, I don't remember exactly options on WinXP, please be patient to find it out).

If after 'ping' enabled and you can 'ping' successful, then you can try to create a alias for the SQL Server and connect via the alias on the client machine.. You'd better enable both TCP/IP and Named Pipe network protocols on the SQL machine. Go to 'Start'->'All Programs'->'Microsoft SQL Server', you can find 'Client Network Utility' and 'Server Network Utility, where you can configure your SQL connection options. The 'Alias' pannel can be found in 'Client Network Utility'.

Hope this helps:)

sqlsql

Connecting to Paradox

Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs =3D CreateObject("ADODB.RecordSet"=AD)
DB_STRING =3D "Provider=3DMicrosoft.Jet.OLEDB.=AD4.0;Data
Source=3DC:\MYDBPath;Extended Properties=3DParadox 5.x;"
strSQL =3D " UPDATE UMTest SET Field1 =3D 1.222 WHERE Col1ID =3D '" & Value1
& "' AND Col2 =3D '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.=20
Thanks.
You may try to create a User DSN first using ODBC Administrator. Instead of
DB_STRING="..", you can set DB_STRING=UserDSNNameYouCreated. To simplify it,
please do not include update command. Try to connect it first and see if it
works.
Thanks
Bei
"Yunus's Group" <yunusasmath@.gmail.com> wrote in message
news:1123266364.868066.278640@.g43g2000cwa.googlegr oups.com...
Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs = CreateObject("ADODB.RecordSet"X)
DB_STRING = "Provider=Microsoft.Jet.OLEDB.X4.0;Data
Source=C:\MYDBPath;Extended Properties=Paradox 5.x;"
strSQL = " UPDATE UMTest SET Field1 = 1.222 WHERE Col1ID = '" & Value1
& "' AND Col2 = '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.
Thanks.

Connecting to Paradox

Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs =3D CreateObject("ADODB.RecordSet"=AD)
DB_STRING =3D "Provider=3DMicrosoft.Jet.OLEDB.=AD4.0;Data
Source=3DC:\MYDBPath;Extended Properties=3DParadox 5.x;"
strSQL =3D " UPDATE UMTest SET Field1 =3D 1.222 WHERE Col1ID =3D '" & Value1
& "' AND Col2 =3D '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.=20
Thanks.You may try to create a User DSN first using ODBC Administrator. Instead of
DB_STRING="..", you can set DB_STRING=UserDSNNameYouCreated. To simplify it,
please do not include update command. Try to connect it first and see if it
works.
Thanks
Bei
"Yunus's Group" <yunusasmath@.gmail.com> wrote in message
news:1123266364.868066.278640@.g43g2000cwa.googlegroups.com...
Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs = CreateObject("ADODB.RecordSet"_)
DB_STRING = "Provider=Microsoft.Jet.OLEDB._4.0;Data
Source=C:\MYDBPath;Extended Properties=Paradox 5.x;"
strSQL = " UPDATE UMTest SET Field1 = 1.222 WHERE Col1ID = '" & Value1
& "' AND Col2 = '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.
Thanks.

Connecting to Oracle 9i Server using ODBC from an SSIS package

I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.

I have tried OLEDB connection and that does not work either.

Any inputs will be appreciated.

Thanks,

Monisha

I use the ODBC Connection in my jobs connecting to Oracle without a problem. Generally this message occurs because the Oracle listener is down. You don't have to specify the port in the SSIS connection manager.|||

I have the DSN created using Microsoft ODBC Driver for Oracle and from the same box I can connect the Oracle server from a SqlPlus session, but when I drag an execute sql task in a SSIS package and create a ODBC connection using system datasource name. I specify the username and password. When I hit the test connection button I get the error that I mentioned earlier.

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

|||Hi,

Im runnign into the same problem. MY DSN works fine when i test in the Data Access tool while creating it. When i use it with in my activex script task it throws an error when i execute a SQL the same error. Did you find a solution for this. Please suggest. Im trying to connect from my DTS Package.

thank you,
Raja

Connecting to Oracle 9i Server using ODBC from an SSIS package

I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.

I have tried OLEDB connection and that does not work either.

Any inputs will be appreciated.

Thanks,

Monisha

I use the ODBC Connection in my jobs connecting to Oracle without a problem. Generally this message occurs because the Oracle listener is down. You don't have to specify the port in the SSIS connection manager.|||

I have the DSN created using Microsoft ODBC Driver for Oracle and from the same box I can connect the Oracle server from a SqlPlus session, but when I drag an execute sql task in a SSIS package and create a ODBC connection using system datasource name. I specify the username and password. When I hit the test connection button I get the error that I mentioned earlier.

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

|||Hi,

Im runnign into the same problem. MY DSN works fine when i test in the Data Access tool while creating it. When i use it with in my activex script task it throws an error when i execute a SQL the same error. Did you find a solution for this. Please suggest. Im trying to connect from my DTS Package.

thank you,
Raja

Connecting to Northwind on SQL Server 2000

I've just installed SQL Server 2000 on my local machine. I'm using the following element in my web.config file for my connection string.

<add key="SqlNWind512" value="server=(local)\141.705.84.745;database=Northwind;
user id=sa;pwd=h3fe8eq7;packet size=512;" /
Unfortunately, I'm getting the following error on my aspx page:

SQL Server does not exist or access denied.

I had no problem writing a connection string to connect to the MSDE installed on my computer, but making a connection to the Northwind database on my newly installed SQL Server 2000 Developer edition has been elusive to me.

I'm sure my IP address is correct. The authentication mode for logging into the database via Enterprise Manager is setup to require a password for sa. Therefore, I assume it's required in the connection string?

Maybe not so coincidentally, while I have been able to open this database through the Server Explorer in VS.NET, I'm not able to access it via Enterprise Mananger (no response, it just hangs) or my aspx pages.

I'm dying here!!! Is my connection string wrong? Any insights would be greatly appreciated!just server=<ip address
forget the (local) bit if it's not local.|||Thank you!|||word to the wise..
Don't put your password to your database online on forums.

h3fe8eq7 can be a very bad thing.

Connecting to MSDE Thru VB Without Machine Name

In order to avoid having to know the machine name, I am thinking about
using the following VB code to connect to an instance of MSDE.
It enumerates all possible SQL Servers and if it finds one with
my instance name string, it uses the string to connect.
I believe it was Andrea who showed me the SQLDMO stuff ...
The code does work on my machine - I am just wondering if it is a
good strategy when deploying the app. to a wide audience.
The installation of MSDE will always install an instance of "SQLINSTEQU".
Public Sub Init_App()
'
' First check that database is running.
'
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Dim Sqlserver_Running As String
Dim msgtext As String
Set oSQLApp = New SQLDMO.Application
Dim ws_Server_Str As String
Dim str_Pos As Integer
Dim ws_EquServer_Str As String
Set oNames = oSQLApp.ListAvailableSQLServers()
Sqlserver_Running = "NO"
'
' Search all available SQL Servers for a \\ServerName\InstanceName
' that has SQLINSTEQU as the instance name. If found, use the whole
' \\ServerName\InstanceName string to connect to the database.
' Hopefully this will work and then do not have to worry about machine
name.
'
For i = 1 To oNames.Count
ws_Server_Str = oNames(i)
strPos = InStr(ws_Server_Str, "SQLINSTEQU")
If strPos > 0 Then
ws_EquServer_Str = ws_Server_Str
Sqlserver_Running = "YES"
Exit For
End If
Next i
If Sqlserver_Running = "NO" Then
msgtext = "The database is not running. Please start your" & _
vbCr & "database and then re-start the application."
MsgBox msgtext
Set oSQLApp = Nothing
Set oNames = Nothing
End
End If
Set oSQLApp = Nothing
Set oNames = Nothing
'
' Establish a connection with the database using the OLE DB provider
' for SQL Server (SQLOLEDB). This provider does not need a data source
' or an existing ODBC driver. It is a native driver for MS SqlServer.
'
cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
";SERVER=" & ws_EquServer_Str & _
";UID=sa" & _
";PWD=abcdefg" & _
";DATABASE=Equ"
cn.Open
End Sub
Paul,
Unfortunately, in practice, the enumeration of SQL Servers is not reliable. I believe that Gert has some
technical elaborations about this on www.sqldev.net, please check that out before decoding on this strategy.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message news:OC7DHeZIEHA.3820@.tk2msftngp13.phx.gbl...
> In order to avoid having to know the machine name, I am thinking about
> using the following VB code to connect to an instance of MSDE.
> It enumerates all possible SQL Servers and if it finds one with
> my instance name string, it uses the string to connect.
> I believe it was Andrea who showed me the SQLDMO stuff ...
> The code does work on my machine - I am just wondering if it is a
> good strategy when deploying the app. to a wide audience.
> The installation of MSDE will always install an instance of "SQLINSTEQU".
>
> Public Sub Init_App()
> '
> ' First check that database is running.
> '
> Dim i As Integer
> Dim oNames As SQLDMO.NameList
> Dim oSQLApp As SQLDMO.Application
> Dim Sqlserver_Running As String
> Dim msgtext As String
> Set oSQLApp = New SQLDMO.Application
> Dim ws_Server_Str As String
> Dim str_Pos As Integer
> Dim ws_EquServer_Str As String
>
> Set oNames = oSQLApp.ListAvailableSQLServers()
> Sqlserver_Running = "NO"
> '
> ' Search all available SQL Servers for a \\ServerName\InstanceName
> ' that has SQLINSTEQU as the instance name. If found, use the whole
> ' \\ServerName\InstanceName string to connect to the database.
> ' Hopefully this will work and then do not have to worry about machine
> name.
> '
> For i = 1 To oNames.Count
> ws_Server_Str = oNames(i)
> strPos = InStr(ws_Server_Str, "SQLINSTEQU")
> If strPos > 0 Then
> ws_EquServer_Str = ws_Server_Str
> Sqlserver_Running = "YES"
> Exit For
> End If
> Next i
>
> If Sqlserver_Running = "NO" Then
> msgtext = "The database is not running. Please start your" & _
> vbCr & "database and then re-start the application."
> MsgBox msgtext
> Set oSQLApp = Nothing
> Set oNames = Nothing
> End
> End If
> Set oSQLApp = Nothing
> Set oNames = Nothing
> '
> ' Establish a connection with the database using the OLE DB provider
> ' for SQL Server (SQLOLEDB). This provider does not need a data source
> ' or an existing ODBC driver. It is a native driver for MS SqlServer.
> '
>
> cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
> ";SERVER=" & ws_EquServer_Str & _
> ";UID=sa" & _
> ";PWD=abcdefg" & _
> ";DATABASE=Equ"
>
> cn.Open
> End Sub
>
|||I searched that site you mentioned and could find nothing that said this
method was unreliable. On the contrary, I found an example that did just
that which can be found here:
http://www.sqldev.net/sqldmo/SamplesVB6.htm
I would appreciate it if you could back up your response with a link.
Thanks anyways ...
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB1$x7ZIEHA.720@.TK2MSFTNGP10.phx.gbl...
> Paul,
> Unfortunately, in practice, the enumeration of SQL Servers is not
reliable. I believe that Gert has some
> technical elaborations about this on www.sqldev.net, please check that out
before decoding on this strategy.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message
news:OC7DHeZIEHA.3820@.tk2msftngp13.phx.gbl...[color=darkblue]
"SQLINSTEQU".
>
|||I phrased it "in practice", but perhaps should have quoted the word "reliable" as well. Anyhow, check out
below:
http://www.sqldev.net/misc/OleDbEnum.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message news:OXBvaHaIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> I searched that site you mentioned and could find nothing that said this
> method was unreliable. On the contrary, I found an example that did just
> that which can be found here:
> http://www.sqldev.net/sqldmo/SamplesVB6.htm
> I would appreciate it if you could back up your response with a link.
> Thanks anyways ...
> Paul
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uB1$x7ZIEHA.720@.TK2MSFTNGP10.phx.gbl...
> reliable. I believe that Gert has some
> before decoding on this strategy.
> news:OC7DHeZIEHA.3820@.tk2msftngp13.phx.gbl...
> "SQLINSTEQU".
>
|||Tibor:
That is NOT the method I proposed.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23zGcXSaIEHA.828@.TK2MSFTNGP12.phx.gbl...
> I phrased it "in practice", but perhaps should have quoted the word
"reliable" as well. Anyhow, check out
> below:
> http://www.sqldev.net/misc/OleDbEnum.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message
news:OXBvaHaIEHA.3144@.TK2MSFTNGP10.phx.gbl...[color=darkblue]
in[color=darkblue]
out[color=darkblue]
about[color=darkblue]
whole[color=darkblue]
machine[color=darkblue]
source[color=darkblue]
SqlServer.
>
|||If you read the DMO documentation, you will see that the DMO ListAvailableSevers method uses the ODBC
SQLBrowseConnect function, which means that the weaknesses that this function has is also in the DMO method.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message news:uBvSUyaIEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Tibor:
> That is NOT the method I proposed.
> Paul
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23zGcXSaIEHA.828@.TK2MSFTNGP12.phx.gbl...
> "reliable" as well. Anyhow, check out
> news:OXBvaHaIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> in
> out
> about
> whole
> machine
> source
> SqlServer.
>
|||Hi Paul,
Believe Tibor on this one. He's dead right.
Even with the new enumeration options shown in the PDC release of Whidbey,
I've seen similar problems. Under the covers, most of these functions rely
on collecting browser packets.
Many of these "browse" type functions take ages to work on the network. Even
common browsing functions can take up to 24 minutes to settle to a stable
state. So, sometimes, they'll work, sometimes they won't.
Even searching for instances on the local system currently involves
searching the registry and making allowances for the different registry
structures created by different versions.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message
news:OXBvaHaIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> I searched that site you mentioned and could find nothing that said this
> method was unreliable. On the contrary, I found an example that did just
> that which can be found here:
> http://www.sqldev.net/sqldmo/SamplesVB6.htm
> I would appreciate it if you could back up your response with a link.
> Thanks anyways ...
> Paul
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:uB1$x7ZIEHA.720@.TK2MSFTNGP10.phx.gbl...
> reliable. I believe that Gert has some
out[vbcol=seagreen]
> before decoding on this strategy.
> news:OC7DHeZIEHA.3820@.tk2msftngp13.phx.gbl...
> "SQLINSTEQU".
machine
>
|||hi Paul,
"Paul McTeigue" <paul_mcteigue@.msn.com> ha scritto nel messaggio
news:uBvSUyaIEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Tibor:
>...
yep... Tibor is right... you always have to believe him =;-D
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434: which is using a UDP broadcast on port
1434, if instance do not listen or not respond on time they will not be part
of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse preceding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
Courtesy of Mr. Gert E.R. Drapers
further Information at
http://sqldev.net/misc.htm
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
the next generation troubles will depend on WinXP service pack 2, which will
default to close all ports on the internal firewall...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Paul,
"Paul McTeigue" <paul_mcteigue@.msn.com> ha scritto nel messaggio
news:%23PePoymIEHA.2480@.tk2msftngp13.phx.gbl...
> Okay - I give - I will not use that method. The whole point was to avoid
> having to know
> the machine name - how do you guys solve this problem?
if you have to, you can resort on SQLBrowseConnect() provided by ODBC if you
do not need max precision, or give a try to the other methods like
NetServerEnum as described in http://sqldev.net/misc.htm
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||
> if you have to, you can resort on SQLBrowseConnect() provided by ODBC if
you
> do not need max precision, or give a try to the other methods like
> NetServerEnum as described in http://sqldev.net/misc.htm
> hth
Hi all
Interesting conversation, I have been reading about this lately and from
what I have read the NetServerEnum API function also has the same problem.
You must try to connect using NetQueryDisplayInformation to ensure you get
around the latency issue with the network browser.
Maybe there is no totally reliable / robust method to do this...
Regards
Daryl

Connecting to local SQL Server

Hi,

I am having problems connecting to databases on my local SQL Server, express edition from my website.

The following is the connection string i am using:

Database=MyDatabase;Server=COMP\SQLEXPRESS;User ID=sa;

The error I get is "Login failed for sa"

I have tried other users but no luck. Also, I cant seem to create a new user as I don't have administrators rights, any ideas how i can do this?

Any ideas?

did you try it this way

<add name="CommerceTemplate" connectionString="Data Source=Comp.Name\SQLEXPRESS;Initial Catalog=dbname;Trusted_Connection=True;"
providerName="System.Data.SqlClient" />

hope this helps

|||

see

www.connectionstrings.com

for help

|||

Hi,

From your description, it seems that you are using "sa" account to log in your SQLServer, that is to say you have set your SQLServer as "SQL Mixed Authentication mode", right?

Based on the error message, there are two possibilities. First, try to log into your SQLServer by SQLServer Management Studio with the 'sa' account, to see if it can work. If not, I guess the account and password maybe invalid. Second, if you are using Windows Authentication mode, you should also make your connection string looks like:

"data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Thanks.

Connecting to AdventureWorks2000_Data.mdf

When I try to connect to this AdventureWorks2000_Data.mdf, I get the following message:

The file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks2000_Data.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

Could not open new database 'AdventureWorks2000'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5118)

Any ideas on how to get the local version installed of SQL Server to open this db? I'm trying to follow along with the examples in the book "Beginning Transact-SQL with SQL Server 2000 and 2005".

Thanks,

Vanzant

Run a sp_helpdb to check all the databases in your server just to make sure you do have the AdventureWorks2000 database. If not, you can download it from

http://www.microsoft.com/downloads/details.aspx?FamilyID=487C9C23-2356-436E-94A8-2BFB66F0ABDC&displaylang=en

|||

Is C: a compressed volume? If so, SQL Server does not support compressed volumes.

Read SQL Server databases are not supported on compressed volumes

|||http://blogs.msdn.com/sanchan/archive/2006/06/04/617585.aspx FYI.|||

I don't think C: is a compressed volume. The only thing I know to check is the disk properties when right click on C:. It isn't selected on the "compress drive to save disk space". If there is another place to check/correct, please let me know.

I can open the AdventureWorks_Data.mdf. I don't know if this matters but I have SQL Server 2005 and SQL Server Express installed on my local PC. This is where I'm trying to look at the files. I'm not sure the differences between the AdventureWorks_Data and AdventureWorks2000_Data, but I don't understand why I can't open the '2000' version.

Any ideas/help would be greatly appreciated.

Thanks,

Vanzant

|||

I think I've figured it out. There are two folders (MSSQL and MSSQL.1) under the Microsoft SQL Server folder. I attached the 2000 db in the MSSQL folder and it worked. I guess it is a newbie issue.

Thanks for inputs!