August 25, 2015 at 3:54 am
Hello Everyone,
I have spent a day and a half on this so far, and have not found anything that works for me yet.
I normally connect to a certain database, and has no problems what-so-ever in my development environment. I develop stand alone apps. Recently I have started playing around with developing websites and adding database connectivity to that - I suspect that this is where my problems started, but cannot pinpoint exactly where... :hehe:
On my local machine (where SQL is installed), I cannot connect using the normal 'sa' user; When I switch authentication to use my local domain user, I can log in, but I cannot do anything worthwhile, as it then bombs out stating I do not have permissions to do it.
When I try logging in from another machine on the domain, I CAN log in fine, using the 'sa' user. However I cannot make any changes either, because it also then states that the 'sa' user does not have permission to perform any action I tried.
I have tried a number of things, suggested in various google search results, here is what I tried:
1. uninstall sql, and re install again
2. disable 'Collation' option for all databases
3. checked that TCP/IP & Named pipes are enabled
4. The server obviously does support remote connections
Any help or suggestions, greatly anticipated.
Thank you!
August 25, 2015 at 4:21 am
There must be at least one sysadmin user able to grant permissions to you or any other user to perform the actions you need.
Normally, when you install SQL Server, you are asked to specify at least one sysadmin user.
If SQL Server is already installed and you don't remember which user was granted sysadmin privileges, you can restart the instance in single user mode and gain sysadmin privileges.
The process is described here: https://msdn.microsoft.com/en-us/library/dd207004.aspx
Hope this helps
-- Gianluca Sartori
August 25, 2015 at 5:45 am
Hi @spaghettidba,
Thanks for your reply. I have followed the advice in the article, and here is my finding (problem still not solved)
On my local machine, with Windows authentication -
I have then restarted sql server in single user mode, created a new user with sysadmin rights
I can still connect via the remote machine to this sql server instance, using the specified credentials, and I could do things such as create a database etc.
HOWEVER, with this new user I could not connect from my local machine, and it stills fails with the same error message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
August 25, 2015 at 7:04 am
can you share more info from the logs, eventviewer
August 25, 2015 at 2:29 pm
Usually, from the local machine you use "shared memory" as trasport protocol for your SQL Server connections. Can you check if this is enabled in the SQL Server configuration manager?
Is this a named instance or a default instance?
If default, can you connect using (local) as servername?
If named, can you connect using (local)\instancename?
Can you ping the server name?
Can you connect to the local port using telnet or portqry?
Do you have entries in your local hosts file?
-- Gianluca Sartori
August 26, 2015 at 12:59 am
Good morning all,
Just thought I would post this. It seems to me that the error message I posted, is a generic error message that SQL uses when it cannot for some reason connect.
In my case, I un-installed the entire SQL packages I previously installed, then went into the Program Files and deleted every file associated to SQL, and then deleted all the registry entries I could find relating to SQL.
I re-installed SQL, and tried to connect again. It failed.
I have installed a named instance, and therefore, in the Server name field, I have something like this: MY-PC-NAME\MY_SQL_INSTANCENAME
I then tried localhost\MY_SQL_INSTANCENAME. -- it failed again.
I then tried MY-IP-ADDRESS\MY_SQL_INSTANCENAME. -- this worked.
I do not know what caused the problem in the first place, as the first convention was working for as long as I can remember.
Main thing is I got it connected again - and hopefully won't suffer a two day inactivity because of related issues.
Thank you so much for your contributions.
Harriet
August 26, 2015 at 1:19 am
To connect to a local SQL Server instance you can use one of these syntaxes:
(local)\MY_SQL_INSTANCENAME
.\MY_SQL_INSTANCENAME
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply