Do you have TCP/IP enabled on the SQL Server as an allowed connection type?
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Gilbert Tordeur" <gilbert.tordeur@.jci.com> wrote in message
news:O56wq5lQIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Hello !
> We use SQL Server 2000 (standard edition) under Windows 2003 Server.
> Due to a hardware failure, we have fully set up the server after having
> formating the hard drives. After having installed Windows Server 2003, we
> have installed SQL Server 2000, then we have restored the databases from
> our last night SQL backup.
> Curiously : all our Windows XP users can connect to the server, but not
> our Windows 2000 users! We have tried to create an ODBC data source with
> Windows authentication : failure. With SQL authentication : failure.
> But the same user, with his same userid, on another PC under Windows XP :
> no problem.
> And it was working well before the crash. Can anybody give us an
> explanation ? a solution ? a help ?
> Thank you,
> Gilbert
>
http://technet.microsoft.com/en-us/library/ms174212.aspx
You need to use the SQL Server configuration manager which has an option for
viewing which connections are allowed...
Its under your start menu programs in the same group as SQL Server...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Gilbert Tordeur" <gilbert.tordeur@.jci.com> wrote in message
news:uiXkOQvQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Hi Warren,
> Thank you for your answer. Could you tell us where to look at, to answer
> your question ?
> Gilbert
> "Warren Brunk" <wbrunk@.techintsolutions.com> a crit dans le message de
> news: eUKerPpQIHA.5980@.TK2MSFTNGP04.phx.gbl...
>
Showing posts with label connection. Show all posts
Showing posts with label connection. Show all posts
Sunday, March 25, 2012
authentication/db connection issues with new setup
Hi,
I've pretty much just thrown together a reporting services (2005)
configuration which connects to a mature sql server 2000 database held
elsewhere. Forgive me for probably not having too much of a clue
about how things should be set up, but I had a go.
I'll get straight to the problem.. that is I can view reports from the
machine running the server, using my own credentials or those of
someone else (tested by running IE7 under an alternative account on
the domain).
However, when using those same user credentials but from a different
machine, I get the following error:
An error has occurred during report processing.
Cannot create a connection to data source 'dsTachyon'.
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Any ideas as to what would be causing this?
cheers,
ChrisMy guess is that it's a "double hop" authentication problem.
You can connect directly to the db when you're on the server, which is a
direct connection. But when you're trying to connect to the db through the
report server, you have one connection hop from your pc to the report
server, and one hop from the report server to the db server. This is called
a double hop.
There are two ways of fixing it.
1) You can connect to the db with a static account that has read access to
the db you get your data from. Instead of using Windows Integrated
Authentication, you use "Credentials stored securely in the report server".
This can be either a SQL account or a Windows account. If you use this, make
sure you're encrypting the data in your report database, in order to encrypt
the password you add.
2) Configure the report server and the db server to use Kerberos, to allow
the credentials to be sent from your pc through the report server to the db
server.
If you want to use Kerberos, start by reading
Configuring Authentication for Reporting Services
http://msdn2.microsoft.com/en-us/library/bb283249.aspx
Specifying Credential and Connection Information
http://msdn2.microsoft.com/en-us/library/ms160330.aspx#
And
Configuring Constrained Delegation for Kerberos (IIS 6.0)
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/df979570-81f6-4586-83c6-676bb005b13e.mspx?mfr=true
You also need to make sure Anonymous Access to the Report Server web
application is not allowed.
My suggestion is to first see if you're able to connect to the data with the
first setup (using a static account). If it works, you should use Kerberos,
as this is a more secure solution. If it doesn't work with a static account,
you might want to work out why before setting up Kerberos, because it's
usually easier, but less secure to make it work with a static account.
Kaisa M. Lindahl Lervik
"Not Me" <clhumphreys@.gmail.com> wrote in message
news:1174310214.836485.140910@.n59g2000hsh.googlegroups.com...
> Hi,
> I've pretty much just thrown together a reporting services (2005)
> configuration which connects to a mature sql server 2000 database held
> elsewhere. Forgive me for probably not having too much of a clue
> about how things should be set up, but I had a go.
> I'll get straight to the problem.. that is I can view reports from the
> machine running the server, using my own credentials or those of
> someone else (tested by running IE7 under an alternative account on
> the domain).
> However, when using those same user credentials but from a different
> machine, I get the following error:
> An error has occurred during report processing.
> Cannot create a connection to data source 'dsTachyon'.
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Any ideas as to what would be causing this?
> cheers,
> Chris
>|||On 19 Mar, 14:00, "Kaisa M. Lindahl Lervik" <kais...@.hotmail.com>
wrote:
> My guess is that it's a "double hop" authentication problem.
> You can connect directly to the db when you're on the server, which is a
> direct connection. But when you're trying to connect to the db through the
> report server, you have one connection hop from your pc to the report
> server, and one hop from the report server to the db server. This is called
> a double hop.
Thank you! Great depth to your reply, I've tried the static account
solution and that works so I'll give Kerberos a look.
Cheers,
Chris
I've pretty much just thrown together a reporting services (2005)
configuration which connects to a mature sql server 2000 database held
elsewhere. Forgive me for probably not having too much of a clue
about how things should be set up, but I had a go.
I'll get straight to the problem.. that is I can view reports from the
machine running the server, using my own credentials or those of
someone else (tested by running IE7 under an alternative account on
the domain).
However, when using those same user credentials but from a different
machine, I get the following error:
An error has occurred during report processing.
Cannot create a connection to data source 'dsTachyon'.
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
Any ideas as to what would be causing this?
cheers,
ChrisMy guess is that it's a "double hop" authentication problem.
You can connect directly to the db when you're on the server, which is a
direct connection. But when you're trying to connect to the db through the
report server, you have one connection hop from your pc to the report
server, and one hop from the report server to the db server. This is called
a double hop.
There are two ways of fixing it.
1) You can connect to the db with a static account that has read access to
the db you get your data from. Instead of using Windows Integrated
Authentication, you use "Credentials stored securely in the report server".
This can be either a SQL account or a Windows account. If you use this, make
sure you're encrypting the data in your report database, in order to encrypt
the password you add.
2) Configure the report server and the db server to use Kerberos, to allow
the credentials to be sent from your pc through the report server to the db
server.
If you want to use Kerberos, start by reading
Configuring Authentication for Reporting Services
http://msdn2.microsoft.com/en-us/library/bb283249.aspx
Specifying Credential and Connection Information
http://msdn2.microsoft.com/en-us/library/ms160330.aspx#
And
Configuring Constrained Delegation for Kerberos (IIS 6.0)
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/df979570-81f6-4586-83c6-676bb005b13e.mspx?mfr=true
You also need to make sure Anonymous Access to the Report Server web
application is not allowed.
My suggestion is to first see if you're able to connect to the data with the
first setup (using a static account). If it works, you should use Kerberos,
as this is a more secure solution. If it doesn't work with a static account,
you might want to work out why before setting up Kerberos, because it's
usually easier, but less secure to make it work with a static account.
Kaisa M. Lindahl Lervik
"Not Me" <clhumphreys@.gmail.com> wrote in message
news:1174310214.836485.140910@.n59g2000hsh.googlegroups.com...
> Hi,
> I've pretty much just thrown together a reporting services (2005)
> configuration which connects to a mature sql server 2000 database held
> elsewhere. Forgive me for probably not having too much of a clue
> about how things should be set up, but I had a go.
> I'll get straight to the problem.. that is I can view reports from the
> machine running the server, using my own credentials or those of
> someone else (tested by running IE7 under an alternative account on
> the domain).
> However, when using those same user credentials but from a different
> machine, I get the following error:
> An error has occurred during report processing.
> Cannot create a connection to data source 'dsTachyon'.
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> Any ideas as to what would be causing this?
> cheers,
> Chris
>|||On 19 Mar, 14:00, "Kaisa M. Lindahl Lervik" <kais...@.hotmail.com>
wrote:
> My guess is that it's a "double hop" authentication problem.
> You can connect directly to the db when you're on the server, which is a
> direct connection. But when you're trying to connect to the db through the
> report server, you have one connection hop from your pc to the report
> server, and one hop from the report server to the db server. This is called
> a double hop.
Thank you! Great depth to your reply, I've tried the static account
solution and that works so I'll give Kerberos a look.
Cheers,
Chris
Thursday, March 22, 2012
Authentication methods for connections to SQL Server in ASP Pages
I am trying to access SQL Server via an ASP page using a Trusted Connection,
but it is not working. When I run the page I receive the following error
message:
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
line 35
My connection string is in a separate file: cst = "data
source=X099789\Widgets;Initial Catalog=Automotive; Integrated Security=SSPI;
"
My code snipet looks like the following:
set OBJRST = Server.CreateObject("ADODB.Recordset")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = cst '****LIne 35
MotorsSQL = "usp_MotorAll"
UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
objConnAll.open cst
What is the correct coding to connect to the SQL Server using Windows
Authentication in an ASP page?
I have read the instructions on:
http://support.microsoft.com/default.aspx/kb/247931, made the changes,
however the page still will not work.
Kindly assist. I will be thankful.
AuntieAuntieAuntie> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
These errors are probably because there is no 'Provider' keyword in your
OLEDB connection string. Try adding 'Provider=SQLOLEDB'.
Hope this helps.
Dan Guzman
SQL Server MVP
"AuntieAuntieAuntie" <AuntieAuntieAuntie@.discussions.microsoft.com> wrote in
message news:EE4C5259-1F64-4893-9AF1-657A1C03917B@.microsoft.com...
>I am trying to access SQL Server via an ASP page using a Trusted
>Connection,
> but it is not working. When I run the page I receive the following error
> message:
> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> line 35
> My connection string is in a separate file: cst = "data
> source=X099789\Widgets;Initial Catalog=Automotive; Integrated
> Security=SSPI;"
> My code snipet looks like the following:
> set OBJRST = Server.CreateObject("ADODB.Recordset")
> Set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = cst '****LIne 35
> MotorsSQL = "usp_MotorAll"
> UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
> objConnAll.open cst
> What is the correct coding to connect to the SQL Server using Windows
> Authentication in an ASP page?
> I have read the instructions on:
> http://support.microsoft.com/default.aspx/kb/247931, made the changes,
> however the page still will not work.
> Kindly assist. I will be thankful.
> AuntieAuntieAuntie
>
but it is not working. When I run the page I receive the following error
message:
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
line 35
My connection string is in a separate file: cst = "data
source=X099789\Widgets;Initial Catalog=Automotive; Integrated Security=SSPI;
"
My code snipet looks like the following:
set OBJRST = Server.CreateObject("ADODB.Recordset")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = cst '****LIne 35
MotorsSQL = "usp_MotorAll"
UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
objConnAll.open cst
What is the correct coding to connect to the SQL Server using Windows
Authentication in an ASP page?
I have read the instructions on:
http://support.microsoft.com/default.aspx/kb/247931, made the changes,
however the page still will not work.
Kindly assist. I will be thankful.
AuntieAuntieAuntie> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
These errors are probably because there is no 'Provider' keyword in your
OLEDB connection string. Try adding 'Provider=SQLOLEDB'.
Hope this helps.
Dan Guzman
SQL Server MVP
"AuntieAuntieAuntie" <AuntieAuntieAuntie@.discussions.microsoft.com> wrote in
message news:EE4C5259-1F64-4893-9AF1-657A1C03917B@.microsoft.com...
>I am trying to access SQL Server via an ASP page using a Trusted
>Connection,
> but it is not working. When I run the page I receive the following error
> message:
> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> line 35
> My connection string is in a separate file: cst = "data
> source=X099789\Widgets;Initial Catalog=Automotive; Integrated
> Security=SSPI;"
> My code snipet looks like the following:
> set OBJRST = Server.CreateObject("ADODB.Recordset")
> Set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = cst '****LIne 35
> MotorsSQL = "usp_MotorAll"
> UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
> objConnAll.open cst
> What is the correct coding to connect to the SQL Server using Windows
> Authentication in an ASP page?
> I have read the instructions on:
> http://support.microsoft.com/default.aspx/kb/247931, made the changes,
> however the page still will not work.
> Kindly assist. I will be thankful.
> AuntieAuntieAuntie
>
Saturday, February 25, 2012
Audit a SQl connection
hi ,
Is it possible to audit a sql Connection, and retrieve all the commands fired from that connection ,
or
how to find all commands fired to a database , as it is shown in " Profiler ".
and its command text .
Use Profiler, run a trace remotely.
Use Third party Auditing products
Audit Tools
ApexSQL Audit http://www.apexsql.com/sql_tools_audit.asp
AuditDatabase (Free Web based trigger generation) http://www.auditdatabase.com/
Lumigent Adit DB http://www.lumigent.com/products/auditdb.html
OmniAudit http://www.krell-software.com/omniaudit/index.asp
SQLLog http://www.rlpsoftware.com/mainframe.asp?contents=SQLLog.asp&mainmenu=SQLLog&submenu=Info
Upscene SQL Log Manager http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm
DB Audit Expert http://www.softtreetech.com/dbaudit/
Thursday, February 16, 2012
Attaching MDF with AttachDBFilename?
I am attempting to attach an .mdf to SQL Express using "AttachDBFilename" in
the connection string, and I have a few questions on the issue.
I am planning on setting this up on a network with both the .mdf and the
instance of SQL Server residing on a server. All the clients will be runnin
g
an app that will have access to the SQL Server instance and the fileshare,
and they will attempt to use a connection string with the "AttachDBFilename"
.
I have successfully connected to the instance of SQL Server and have access
to the .mdf from a client, but I have not been able to get the connection to
attach the .mdf file. If I run the app on the server it works, but on a
client I get an error saying something like "The file '..' is on a network
path that is not supported for database files."
My questions are:
Can this be done using "AttachDBFilename"?
If yes, any suggestion on what I may be missing?
Even if I do get this method to work will it support multiple users?
Thanks,
JimThe database file (mdf) only needs to be attached one time -at the server.
Clients only need to connect to the server and have access to the database.
If SQL Express was installed on each client, and those clients used the
'AttachDBFilename', they would be attempting to attach a database file to
their local copy of SQL Express. Database files cannot be attached from a
unc network path. They would each need a local copy of the database for that
to work. However, it sounds like you want a centrally located database, not
individual copies.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:5732D62A-EE09-4ED6-8548-FFB7AC58EC2E@.microsoft.com...
>I am attempting to attach an .mdf to SQL Express using "AttachDBFilename"
>in
> the connection string, and I have a few questions on the issue.
> I am planning on setting this up on a network with both the .mdf and the
> instance of SQL Server residing on a server. All the clients will be
> running
> an app that will have access to the SQL Server instance and the fileshare,
> and they will attempt to use a connection string with the
> "AttachDBFilename".
> I have successfully connected to the instance of SQL Server and have
> access
> to the .mdf from a client, but I have not been able to get the connection
> to
> attach the .mdf file. If I run the app on the server it works, but on a
> client I get an error saying something like "The file '..' is on a network
> path that is not supported for database files."
> My questions are:
> Can this be done using "AttachDBFilename"?
> If yes, any suggestion on what I may be missing?
> Even if I do get this method to work will it support multiple users?
> --
> Thanks,
> Jim
the connection string, and I have a few questions on the issue.
I am planning on setting this up on a network with both the .mdf and the
instance of SQL Server residing on a server. All the clients will be runnin
g
an app that will have access to the SQL Server instance and the fileshare,
and they will attempt to use a connection string with the "AttachDBFilename"
.
I have successfully connected to the instance of SQL Server and have access
to the .mdf from a client, but I have not been able to get the connection to
attach the .mdf file. If I run the app on the server it works, but on a
client I get an error saying something like "The file '..' is on a network
path that is not supported for database files."
My questions are:
Can this be done using "AttachDBFilename"?
If yes, any suggestion on what I may be missing?
Even if I do get this method to work will it support multiple users?
Thanks,
JimThe database file (mdf) only needs to be attached one time -at the server.
Clients only need to connect to the server and have access to the database.
If SQL Express was installed on each client, and those clients used the
'AttachDBFilename', they would be attempting to attach a database file to
their local copy of SQL Express. Database files cannot be attached from a
unc network path. They would each need a local copy of the database for that
to work. However, it sounds like you want a centrally located database, not
individual copies.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:5732D62A-EE09-4ED6-8548-FFB7AC58EC2E@.microsoft.com...
>I am attempting to attach an .mdf to SQL Express using "AttachDBFilename"
>in
> the connection string, and I have a few questions on the issue.
> I am planning on setting this up on a network with both the .mdf and the
> instance of SQL Server residing on a server. All the clients will be
> running
> an app that will have access to the SQL Server instance and the fileshare,
> and they will attempt to use a connection string with the
> "AttachDBFilename".
> I have successfully connected to the instance of SQL Server and have
> access
> to the .mdf from a client, but I have not been able to get the connection
> to
> attach the .mdf file. If I run the app on the server it works, but on a
> client I get an error saying something like "The file '..' is on a network
> path that is not supported for database files."
> My questions are:
> Can this be done using "AttachDBFilename"?
> If yes, any suggestion on what I may be missing?
> Even if I do get this method to work will it support multiple users?
> --
> Thanks,
> Jim
Sunday, February 12, 2012
attached in vb8 vs vb6 incompatability
I have two programs which access sql server 2005. One was written in vb6 the other in vb8 (aka vb.net 2005).
vb6 works well with this connection string:
Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Database=MyDbName;Data Source=.\MyServerInstanceName
vb8 (in vs2005) generated the following connection string which works well in the vb8 application:
Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True
Within their own programs each connection string works well. However, if I use one program and then the other (e.g., vb8 program then vb6), I get an error message:
Unable to open the physical file "C:\MyDbPath\MyDbName.mdf". Operating system error 32: "32(The process cannot acces the file because it is being used by another process.)".
If I detach the database using SSMS, I can then access it with the other program. Obviously, this is not an ideal situation and not something I would want the end user to have to do.
Also, when using the vb6 connection string, SSMS lists the database in Object Explorer as: MyDbName. When using the vb8, the database is listed as C:\MyDbPath\MyDbName.mdf. BTW, the database is set for multi-user.
How can I get the two programs to place nice together?
YOu can either use the database file with one process or attach the database to a SQL Server (Express) instance to make it accessible for more than one application.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
When you use the attach command in the connection string the application that is running the command has exclusive access to the files, If you want the database to be used by multiple applications you will need to first make sure that the database will allow remote connections then attach the database to the database engine using the management tools or the command line tools. Once this is done you would then just connect using the following string
"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
The above connection string was taken from www.connectionstrings.com
|||
The database absolutely must be used by more than one program. SQL Server Express is not an option because of its 4gb limit. Perhaps I'm not understanding something here. It sounds as tho you are saying an SQL Server database cannot be used by more than one program at the same time unless the database is so small that it is unusable for all except the simplest of applications.
|||Again, perhaps I am missing something. Are you saying SQL Server 2005 cannot be used by multiple users unless they are all using the same program, or they have enough database management experience to use tools such as SSMS, or even more shocking still: they have to use command line tools? This is starting sound like Linux and MySQL.
It is shocking to imagine that everyday users would have to have such extensive skills just to use database enabled Windows XP? applications.
|||Yes, you are right, you got me wrong :-)OK, a database file which is not attached can be attached as a user instance with a SQL Server Express instance. As a user instance, only the application which opened the connection to the user instance is able to access the database. If you attach the database file to a server instance, you can access the data through the SQL Server Express Service as you already know it from the common SQL Server database access.
Hope that clarifies things.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Sorry, but no.
Where am I using the user instance feature?
|||Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True|||Now, I am confused
The user instance login flag is not supported on this version of SQL Server. The connection will be closed.
|||I resolved the problem by starting both vb6 & vb8 programs with a check for whether or not the database is attached. If it is not, I use sp_attach_single_file_db to attach it. Then I changed the part of the vb8 connection AttachDbFilename= to Database=.AttachDBFileName refers to original log file
Hello,
I'm using the AttachDBFilename option in the SQL connection string to attach
a database mdf file copied to another machine.
There is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMaster\Database\Strategic.TimeMaster.Database.SQL2005\TimeMasterDat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?
Coudl you please post your connection string here ? Seems that you are mixing up the setting for server / user instances.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Here it is:
Data Source=.\SQLExpress;AttachDBFileName=C:\Program Files\Time Master\TimeMasterDat.mdf;Initial Catalog=TimeMaster;Trusted_Connection=yes;
The mdf file should be attached to a local instance of SQLExpress. The mdf has been copied from my development machine and the it seems to think that this mdf file is refering to this log file.
It shouldn't, it should create a log file on the local machine.
|||Try to add the "User Instance=true;" to the connectionstring.|||That worked, but creating another User Instance was not really what I was looking to do, but will probably do
Thanks
|||Did you want to attach the database to the server for a server instance ? or did you want to use the user instance feature of SQL Express ? If you want to use the user instance you WILL have to create one instance per database.-Jens.|||A server instance I suppose. The server and client being on the same machine.|||
If you want to use a server attached instance you will have to attach the database first (if you have no log LDF file you will have to try to attach the database using sp_attach_single_file_db) using sp_attachdb or the equivalent GUI action in the managment studio.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
AttachDBFileName option in Connection String
I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"
Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"
However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.
After adding "Database=Registration.mdf;server=<server N/W address>" and removing
"AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.
Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?
Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?
You might look following link. you need to provide the database name along with the DB filename
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
|||Are database name & DB Filename not the same? Do you mean complete DB Path when you say DB FileName?Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?
Also the ODBC driver Connection String mentions following:
"The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"
Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name?
|||
Here.. what u need to understand is ... u r using the unique Embedded Database feature of SQL Server Express. U r dynamically attaching the database.
Refer this link it will help u
Connecting to SQL Express User Instances in Management Studio
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.
You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it. The syntax of a server name is <Server>\<instance>. Note that a blank <instance> is equated to the default instance. In your case, the web server is running a default instance of SQL Server. The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS. You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.
Hope that cleared things up for you,
John
AttachDBFileName option in Connection String
I used the following connection string when working with a SQL server DB locally in an ASP.NET code-behind module:
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"
Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?
dim source as string="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Registration.mdf;User Instance=true"
However, when I uploaded the application to a web server where my site is hosted, I spent considerable time debugging the "Cannnot establish connection" exception, I was getting.
After adding "Database=Registration.mdf;server=<server N/W address>" and removing
"AttachDBFilename=|DataDirectory|Registration.mdf;" from the connection string, the connection was finally established.
Now, I have two questions: What's the difference between database and attachDBFileName options (And when should I use which)?
Secondly, why I had to add the server option, when it worked fine locally, and the application & db are located on the same web server?
You might look following link. you need to provide the database name along with the DB filename
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
|||Are database name & DB Filename not the same? Do you mean complete DB Path when you say DB FileName?Moreover, what is an attachable Database? The primary file of an attachable database is the default primary data file or it can be any primary data file?
Also the ODBC driver Connection String mentions following:
"The database must be an existing database unless AttachDBFileName is also specified. If AttachDBFileName is also specified, the primary file it points to is attached and given the database name specified by Database"
Does that mean that if Database attribute contains a non-existent DB name, but AttachDBFileName contains a valid primary filename, then a database is created by that name?
|||
Here.. what u need to understand is ... u r using the unique Embedded Database feature of SQL Server Express. U r dynamically attaching the database.
Refer this link it will help u
Connecting to SQL Express User Instances in Management Studio
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.
You had to change data source=.\SQLEXPRESS on the production server because it did not have a named instance of SQL Express running on it. The syntax of a server name is <Server>\<instance>. Note that a blank <instance> is equated to the default instance. In your case, the web server is running a default instance of SQL Server. The option to install a default instance is available in SQLEXPRESS as well, though you have to select it explicitly or else it installs as a named instance with the name SQLEXPRESS. You could have used (local) or localhost or . or <COMPUTERNAME> instead of the IP on the server, so long as you don't specify an absent instance name.
Hope that cleared things up for you,
John
AttachDBFileName - refers to previous log file
Hello,
I'm using the AttachDBFilename option in the SQL connection string to attach
a database to a SQLExpress instance on another machine.
The is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMaster\Databas e\Strategic.TimeMaster.Database.SQL2005\TimeMaster Dat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?
Answered in
http://forums.microsoft.com/forums/ShowPost.aspx?PostID=1081110&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
I'm using the AttachDBFilename option in the SQL connection string to attach
a database to a SQLExpress instance on another machine.
The is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMaster\Databas e\Strategic.TimeMaster.Database.SQL2005\TimeMaster Dat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?
Answered in
http://forums.microsoft.com/forums/ShowPost.aspx?PostID=1081110&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
AttachDBFileName - refers to previous log file
Hello,
I'm using the AttachDBFilename option in the SQL connection string to attach
a database to a SQLExpress instance on another machine.
The is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMast
er\Database\Strategic.TimeMaster.Dat
abase.SQL2005\TimeMasterDat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?Answered in
http://forums.microsoft.com/forums/...081110&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
I'm using the AttachDBFilename option in the SQL connection string to attach
a database to a SQLExpress instance on another machine.
The is an error though, it seems to want to refer to a log file on the
machine the .mdf was originally created on. The error is as follows:
Could not attach file 'C:\Program Files\Strategic Systems\Time
Master\TimeMasterDat.mdf' as database 'TimeMaster'.
File activation failure. The physical file name "D:\Visual Studio 2005
Projects\Strategic\Applications\TimeMast
er\Database\Strategic.TimeMaster.Dat
abase.SQL2005\TimeMasterDat_log.LDF" may be incorrect
How can I get the attach to avoid referring to this log file?Answered in
http://forums.microsoft.com/forums/...081110&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
AttachDBfilename
I was getting SQL Server errors trying to run the Time Tracker kit, soI tried to establish a connection with the timetracker.mdf from the
VWD Database Explorer - but no luck:
VWD Database Explorer - but no luck:
Data Source: Microsoft SQL Server Database File (SqlClient)
Database File name: Visual Studio2005\WebSites\TimeTracker1\App_Data\TimeTracker.mdf
Using either Windows or SQLServer authentication, they both fail when I test the connection.
Any ideas ? I gave myself and the ASPNET account every permission possible in SQLServer.Create a connection from a datasource control to test it or add a connection in VWD in server explorer ( i hope they have that hehe I use VS.NET 2005 pro ). Test the connection until it works. I had to play with it until I got it to work but I had the same problem. Make sure you are saying that you want a user instantce. First make a connection to your db and make sure that works. Then try to attach with the db.
Subscribe to:
Posts (Atom)