November 25, 2008 at 4:57 am
I can connect to Integration Services, but not to Database engine or to Analysis services. Integration Services shows the default instance in the "connect to server" dialogue box, but the others show no server names available. I am using windows authentication.
I also installed SQL Server 2005 SP2, which supposedly allows it to work with Vista.
Any ideas?
November 25, 2008 at 5:03 am
If you are sure that you install SSAS during the installation, be sure that your service is running at the SQL Server Configuration Manager!
November 25, 2008 at 5:34 am
Thanks and yes, SSAS was installed and is running. I guess the more major factor is that I can't connect to a server with the database engine. I can live without analysis services, I mentioned it just to give more info.
November 26, 2008 at 6:09 am
Integration Services is shared and by it's default server connection is "." or in other words "localhost". The other components mentioned are installed on a per instance basis. Did you use a named instance during installation? Just hit browse (assuming the SQL Browser service is running on your server) and it will show you the exact name to use for connecting. You can also find the name of the instance is the Services manager (Start, Run, services.msc). Scroll do to SQL Server and you will named the named instance in parenthesis after it.
Benjamin Lotter
http://BenjaminLotter.info/[/url]
http://www.LinkedIn.com/in/BenjaminLotter
Delight thyself also in the LORD and He shall give thee the desires of thine heart.
~Psalm 37:4
November 26, 2008 at 7:50 am
Thanks Benjamin. I am using the default instance MSSQLSERVER. I see this INstance name in parens in the services manager. When I try to connect to this server name I get the second message below.
Interestingly, if I browse for a server name, I find DPG - my computer name, in the network servers database engine folder. When I try to connect to this one I get the following error message:
TITLE: Connect to Server
------------------------------
Cannot connect to DPG.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'dpg\dg'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
Not sure why DPG shows up in network servers, rather than local.
Thanks again for your help.
Here is the message I get when I try to connect to MSSQLSERVER
===================================
Cannot connect to SQL Server(MSSQLSERVER).
===================================
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) (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
------------------------------
Error Number: 53
Severity: 20
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
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.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
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 Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
November 26, 2008 at 9:24 am
By default, when you install SQL, it adds the group "BUILTIN\Administrators" to the SQL Server with the sysadmin server role. The fact that get this error indicates that you have not been explicity granted permissions to that server nor are you in the Administrators group on the DPG server:
Login failed for user 'dpg\dg'
Do you have the SA password or did you install this in Integrated mode with a disabled SA?
Benjamin Lotter
http://BenjaminLotter.info/[/url]
http://www.LinkedIn.com/in/BenjaminLotter
Delight thyself also in the LORD and He shall give thee the desires of thine heart.
~Psalm 37:4
November 26, 2008 at 12:08 pm
Check database engine is running ... ie SQL server service is running in services from control panel... administration.... services.
If not running.... start the service and check.
If you are connecting from differenct system check the server accepts remote connections.
November 26, 2008 at 1:06 pm
I installed without an SA, I believe I told it to use windows authentication, so that my windows login would work, but dg does not work as you see.
The SA password looked like it was already filled in with a long *'d value.
I've tried logging in with the Administrators ID, but did not know the password. Any ideas there?
Do you suggest re-installing and providing my own SA credentials?
Thanks again Ben.
November 26, 2008 at 1:12 pm
Grasshopper,
The service is running. Even though I tried to install so that the instance is on my local machine, where do I check server accepts remote connections?
Thanks.
November 26, 2008 at 1:23 pm
Benjamin Lotter (11/26/2008)
By default, when you install SQL, it adds the group "BUILTIN\Administrators" to the SQL Server with the sysadmin server role.
Not on Vista or Server 2008. On those two accounts have to be added explicitly. SQL 2008 does that during setup. For SQL 2005 trhe installation of SP2 does it automatically, or you can run a tool called SqlProv.exe (will be in the install dir for SQL if you're on SP2 or higher. For me it's in the dir C:\Program Files (x86)\Microsoft SQL Server\90\Shared)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 1:35 pm
Thanks Gail. I'm installing 2005 on vista. I see sqlprov.exe, does it do anything besides creating the BUILTIN\Administrators id? If I then try and log in with this id, should I use SQL Server authentication? Do you know the default password?
Thanks Again.
November 26, 2008 at 1:54 pm
It doesn't add builtin\administrators. It adds windows accounts that you specify to SQL server and makes them sysadmin. There's no good reason not to run it.
If your server is set up for windows authentication only, you can't use a sql login at all, and there isn't a default password these days.
Check the error log to see if you're using windows authent only or mixed mode (it's just a textfile, open it with notepad), but if you haven't configured anything, then it'll be windowes authent only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 2:21 pm
Now we are getting somewhere, do you know how to:
verify that SQL Server is configured in Mixed Authentication Mode, and if it is NOT, then how to change it?
Triple Thanks.
November 26, 2008 at 2:27 pm
dg (11/26/2008)
Now we are getting somewhere,
I'm sorry if the support here isn't up to your expectations.
verify that SQL Server is configured in Mixed Authentication Mode
As I said in my previous post.
Check the error log to see if you're using windows authent only or mixed mode (it's just a textfile, open it with notepad)
There will be a line very close to the top that will say what the authentication mode is.
The error log file is called ERRORLOG
It's location depends on your installation setting, but it should be easy to search for.
and if it is NOT, then how to change it?
Using management studio, open the SQL instance in object explorer. Right click the server, go to security. The option's there. You will need to restart the service for the setting to take affect.
I believe you need sysadmin rights to change that setting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 2:52 pm
dg (11/26/2008)
Grasshopper,The service is running. Even though I tried to install so that the instance is on my local machine, where do I check server accepts remote connections?
Thanks.
For SQL Server 2005 type "SQLServerManager.msc" in Start, Run. For 2008 this is "SQLServerManager10.msc". It should also be accessible through Start, Program, Microsoft SQL Server 200X, Configuration Tools, Sql Server Configuration Manager. Under the SQL Server 200X Network Configuration, Protocols for YOURINSTANCE. Then check the TCP/IP protocol to see if its enabled. Is the SQL Server Browser service running? Can you ping the server? Can you telnet to the IP:port?
Again, these are all good to check, but I don't think it will help since your error message indicated that you could connect but your credentials failed. Are you a local adminstrator of that server?
Benjamin Lotter
http://BenjaminLotter.info/[/url]
http://www.LinkedIn.com/in/BenjaminLotter
Delight thyself also in the LORD and He shall give thee the desires of thine heart.
~Psalm 37:4
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply